Re: [GENERAL] [PGSQL 8.3.5] How to handle FKs with partitioning?
My idea is that DRI will help during the the JOINs I'll need to make later. Creating a trigger to check the consistence would not help for that case, unless my idea is wrong. In which case I'd follow the great Merlin's hint. So the question is now: do DRI impact on JOINs efficiency? What'd be the gain? The table in question should easily go 20+M rows, possibly up to 50+M a year. The partitioning would ensure about 2M rows per partition and the trigger should work accordingly to this (dynamic) schema. So, along with the loss of efficiency due to the trigger I also would get some other loss because of an external table needed for the partitioning. On Friday December 19 2008 17:15:56 Merlin Moncure wrote: > On Fri, Dec 19, 2008 at 6:04 AM, Reg Me Please wrote: > > Hi all. > > > > I need to implement something very similar to temporal table partitioning > > as described in the documentation at chapter 5.9. > > > > My issues come from the fact that I have other tables that references > > (FKs) to the table(s) to be partitioned. Those references are enforced by > > means of DRI statements (REFERENCES ...). > > > > As the table containing the referenced data will not be a single table, > > will I be forced to drop DRI? > > The referencing table(s) don't need to be partitioned, though and have > > also other FKs to other tables. > > > > Is there any other solution as I would keep DRI? > > Write a trigger. > > merlin -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PGSQL 8.3.5] How to handle FKs with partitioning?
> On Friday December 19 2008 17:15:56 Merlin Moncure wrote: >> On Fri, Dec 19, 2008 at 6:04 AM, Reg Me Please >> > I need to implement something very similar to temporal table partitioning >> > as described in the documentation at chapter 5.9. >> > >> > My issues come from the fact that I have other tables that references >> > (FKs) to the table(s) to be partitioned. Those references are enforced by >> > means of DRI statements (REFERENCES ...). >> >> Write a trigger. >> On Sat, Dec 20, 2008 at 3:51 AM, Reg Me Please wrote: > My idea is that DRI will help during the the JOINs I'll need to make later. > > Creating a trigger to check the consistence would not help for that case, > unless my idea is wrong. In which case I'd follow the great Merlin's hint. > > So the question is now: do DRI impact on JOINs efficiency? What'd be the gain? > > The table in question should easily go 20+M rows, possibly up to 50+M a year. > The partitioning would ensure about 2M rows per partition and the trigger > should work accordingly to this (dynamic) schema. > So, along with the loss of efficiency due to the trigger I also would get some > other loss because of an external table needed for the partitioning. DRI (referential integrity) is not a performance option. It is strictly for correctness. PostgreSQL will be able to do joins strictly looking at the data types and the indexes that may be on them. I'm not a huge fan of the built in replication...it can create as many problems as it solves. The main reason to do it is for faster VACUUMs and things like that, or perhaps if you need to frequently add/remove records in bulk (like a log rotation system). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Custom Type Alignment
Simple question on CREATE TYPE (I hope): When using the "alignment" option, and setting it to "double", what ends up double aligned? VARDATA()? The whole thing datum (so that the alignment of VARDATA() is actually conditioned on the size of VARHDRSZ) ? Thanks, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Custom Type Alignment
"Paul Ramsey" writes: > Simple question on CREATE TYPE (I hope): > When using the "alignment" option, and setting it to "double", what > ends up double aligned? VARDATA()? The whole thing datum (so that the > alignment of VARDATA() is actually conditioned on the size of > VARHDRSZ) ? No, the value as a whole. If you're talking about a varlena-format value then the length word will start on a double boundary, and you'll have to waste a word of alignment padding if the data payload has to start on a double boundary. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] infinity interval
Hello, how can I store an infinity value into an interval? I want to store offsets to a timestamp, and for some cases i need +infinity and -infinity as result. I want to make something like this: SELECT now() + 'infinity'::interval; This should return 'infinity'::timestamp. Is it possible? Thanks, Gerhard signature.asc Description: Digital signature
Re: [GENERAL] infinity interval
On Sat, Dec 20, 2008 at 3:52 PM, Gerhard Heift wrote: > how can I store an infinity value into an interval? I don't think you can: postgres=# select INTERVAL '1 week' + 'infinity'::timestamp; ?column? -- infinity (1 row) postgres=# select INTERVAL 'infinity' + now(); ERROR: invalid input syntax for type interval: "infinity" Also notice the limits that the Interval datatype supports. http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html > I want to store > offsets to a timestamp, and for some cases i need +infinity and > -infinity as result. You might need to rethink your design to two timestamps, or use a null represent to concept of infinite duration since postgresql doesn't currently support this. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] infinity interval
Gerhard- did you try to use a PL/SQL Block to initialize the necessary integer values in your declare block e.g. DECLARE SET @INT_MIN = INTEGER ( min ); --Min Value SET @INT_MAX = INTEGER ( max ); --Max value BEGIN END; http://pgscript.projects.postgresql.org/SCRIPT.html HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Sun, 21 Dec 2008 00:52:04 +0100 > From: ml-postgresql-20081012-3...@gheift.de > To: pgsql-general@postgresql.org > Subject: [GENERAL] infinity interval > > Hello, > > how can I store an infinity value into an interval? I want to store > offsets to a timestamp, and for some cases i need +infinity and > -infinity as result. > > I want to make something like this: > SELECT now() + 'infinity'::interval; > > This should return 'infinity'::timestamp. > > Is it possible? > > Thanks, > Gerhard _ Send e-mail faster without improving your typing skills. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008
Re: [GENERAL] infinity interval
Gerhard Heift wrote: > how can I store an infinity value into an interval? I want to store > offsets to a timestamp, and for some cases i need +infinity and > -infinity as result. > > I want to make something like this: > SELECT now() + 'infinity'::interval; > > This should return 'infinity'::timestamp. > > Is it possible? TODO has: o Allow infinite intervals just like infinite timestamps -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
