Temporal Extensions for PostgreSQL
by: Warren Turkal
I would like to see a comprehensive solution to time varying tables (or
temporal) in PostgreSQL. I specifically want to see suuport for valid-time and
transacation-time and bitemporal (valid-time and transaction-time) tables. I
will be defering
Bruce Momjian wrote:
> Tom Lane wrote:
> > Gregory Stark <[EMAIL PROTECTED]> writes:
> > > That seems like an awful lot of copying and pallocs that aren't there
> > > currently though. And it'll make us reluctant to change over frequently
> > > used
> > > data types like text -- which are precisel
Almost a year ago, we talked about the problem that referential
integrity should be selecting comparison operators on the basis
of b-tree index opclasses, instead of assuming that the appropriate
operator is always named "=":
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php
http://
Tom Lane wrote:
> Gregory Stark <[EMAIL PROTECTED]> writes:
> > That seems like an awful lot of copying and pallocs that aren't there
> > currently though. And it'll make us reluctant to change over frequently used
> > data types like text -- which are precisely the ones that would gain us the
> >
Simon Riggs wrote:
> > I need clarification here. Is removing dead heap tuple always going to
> > require an index scan, or was this just for chilling a row (adding an
> > index)?
>
> We can remove a tupled marked HEAP_ONLY_TUPLE when it is status
> HEAPTUPLE_DEAD. The HEAP_UPDATE_ROOT tuple can
Gregory Stark <[EMAIL PROTECTED]> writes:
> That seems like an awful lot of copying and pallocs that aren't there
> currently though. And it'll make us reluctant to change over frequently used
> data types like text -- which are precisely the ones that would gain us the
> most.
> It seems to me th
On Fri, 2007-02-09 at 18:10 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > > Removing the root tuple will require a VACUUM *FULL*.
> >
> > That seems unacceptable ... it won't take too long for your table to
> > fill up with stubs, and we don't want to return to the bad old days
> > when period
"Bruce Momjian" <[EMAIL PROTECTED]> writes:
>> Uh. So I don't see how to make this work on a little-endian machine. If the
>> leading its are 0 we don't know if they're toast flags or bits on the least
>> significant byte of a longer length.
>> ...
> I had forgotten about hooking into the TOAST s
Tom Lane wrote:
> > Removing the root tuple will require a VACUUM *FULL*.
>
> That seems unacceptable ... it won't take too long for your table to
> fill up with stubs, and we don't want to return to the bad old days
> when periodic VACUUM FULL was unavoidable.
>
> ISTM we could fix that by exten
On Fri, 2007-02-09 at 13:16 +0530, Pavan Deolasee wrote:
> The second problem of concurrent index scans seems a bit more complex.
> We need a mechanism so that no tuples are missed or tuples are
> not returned twice. Since CHILLing of a tuple adds a new access path
> to the tuple from the index,
On 2/9/2007 3:25 PM, Andrew Dunstan wrote:
Richard Troy wrote:
On Fri, 9 Feb 2007, Andrew Dunstan wrote:
Richard Troy wrote:
In more specific terms, and I'm just brainstorming in public here, perhaps
we can use the power of Schemas within a database to manage such
divisions; commands w
Greg Stark wrote:
> Tom Lane <[EMAIL PROTECTED]> writes:
>
> > Greg Stark <[EMAIL PROTECTED]> writes:
> > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > >> I know it is kind of odd to have a data type that is only used on disk,
> > >> and not in memory, but I see this as a baby varlena type, used
I wrote:
We do now have schema nesting, for this or any other purpose.
s/now/not/ (of course)
cheers
andrew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Richard Troy wrote:
On Fri, 9 Feb 2007, Andrew Dunstan wrote:
Richard Troy wrote:
In more specific terms, and I'm just brainstorming in public here, perhaps
we can use the power of Schemas within a database to manage such
divisions; commands which pertain to replication can/would includ
On 2/9/2007 2:19 PM, Andrew Hammond wrote:
On Feb 7, 8:12 pm, [EMAIL PROTECTED] (Bruce Momjian) wrote:
Jan Wieck wrote:
> On 2/7/2007 10:35 PM, Bruce Momjian wrote:
> > I find the term "logical proof of it's correctness" too restrictive. It
> > sounds like some formal academic process that real
On Fri, 2007-02-09 at 11:46 -0500, Tom Lane wrote:
> "Jim Nasby" <[EMAIL PROTECTED]> writes:
> > IF I run the following with the a < 2900 condition first, the more
> > expensive EXISTS only gets executed when needed, but if I change the
> > order of the OR's, the EXISTS is always executed. It w
On Fri, 9 Feb 2007, Jan Wieck wrote:
>
> No matter how many different models you have in parallel, one single
> transaction will be either a master, a slave or an isolated local thing.
> The proposed changes allow to tell the session which of these three
> roles it is playing and the triggers and
On Fri, 9 Feb 2007, Andrew Dunstan wrote:
> Richard Troy wrote:
> > In more specific terms, and I'm just brainstorming in public here, perhaps
> > we can use the power of Schemas within a database to manage such
> > divisions; commands which pertain to replication can/would include a
> > schema sp
On 2/9/2007 2:27 PM, Richard Troy wrote:
In general terms, "blending of replication [techniques]" means to me that
one can have a single database instance serve as a master and as a slave
(to use only one set of terminology), and as a multi-master, too, all
simultaneously, letting the DBA / Archi
Richard Troy wrote:
In more specific terms, and I'm just brainstorming in public here, perhaps
we can use the power of Schemas within a database to manage such
divisions; commands which pertain to replication can/would include a
schema specifier and elements within the schema can be replicated on
On Feb 7, 8:12 pm, [EMAIL PROTECTED] (Bruce Momjian) wrote:
> Jan Wieck wrote:
> > On 2/7/2007 10:35 PM, Bruce Momjian wrote:
> > > I find the term "logical proof of it's correctness" too restrictive. It
> > > sounds like some formal academic process that really doesn't work well
> > > for us.
>
>
On Fri, 2007-02-09 at 13:47 -0500, Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > What are the problems with just shuffling the last (and only visible)
> > tuple to replace the HOT-hain root and be done with it ?
>
> ctid stops being a reliable identifier.
Yes, that sums it up.
On Fri, 9 Feb 2007, Jan Wieck wrote:
> > [ I wrote ]
> > It'd be great if Jan considers the blending of replication;
>
> Please elaborate. I would really like to get all you can contribute.
Thanks Jan,
prefaced that I really haven't read everything you've written on this (or
what other people ar
Hannu Krosing <[EMAIL PROTECTED]> writes:
> What are the problems with just shuffling the last (and only visible)
> tuple to replace the HOT-hain root and be done with it ?
ctid stops being a reliable identifier.
regards, tom lane
---(end of broadc
Ühel kenal päeval, R, 2007-02-09 kell 13:39, kirjutas Heikki
Linnakangas:
> Tom Lane wrote:
> > ISTM we could fix that by extending the index VACUUM interface to
> > include two concepts: aside from "remove these TIDs when you find them",
> > there could be "replace these TIDs with those TIDs when
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> I know it is kind of odd to have a data type that is only used on disk,
> >> and not in memory, but I see this as a baby varlena type, used only to
> >> store and get va
Simon Riggs wrote:
On Fri, 2007-02-09 at 13:39 +, Heikki Linnakangas wrote:
Tom Lane wrote:
ISTM we could fix that by extending the index VACUUM interface to
include two concepts: aside from "remove these TIDs when you find them",
there could be "replace these TIDs with those TIDs when you
On Feb 9, 2007, at 10:46 AM, Tom Lane wrote:
"Jim Nasby" <[EMAIL PROTECTED]> writes:
IF I run the following with the a < 2900 condition first, the more
expensive EXISTS only gets executed when needed, but if I change the
order of the OR's, the EXISTS is always executed. It would be good if
the o
>> I thought you said this was just syntactic sugar for capabilities we
>> already had?
> My mistake. I am sorry. I have to store somewhere flag. One bit, which
> signalise "don't use OUT arguments as function's parameters".
Huh? What exactly is the meaning of the arguments then?
It sounds to
"Mark Cave-Ayland" <[EMAIL PROTECTED]> writes:
> On Thu, 2007-02-08 at 20:49 -0500, Bruce Momjian wrote:
>> Who is working on this item?
>
> Jonah was trying to complete this for 8.3, but I believe that he has
> handed it onto Gregory Stark - I think
> http://archives.postgresql.org/pgsql-hackers
On Fri, 2007-02-09 at 13:39 +, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > ISTM we could fix that by extending the index VACUUM interface to
> > include two concepts: aside from "remove these TIDs when you find them",
> > there could be "replace these TIDs with those TIDs when you find them
Pavel Stehule wrote:
> >OK, where are we on this patch?
>
> without changes. This task have to do anybody who better know PostgreSQL
> cache system than me.
How about you submit a version without any caching, but which works
correctly; and we worry about optimizations later?
I can update and
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
>> I thought you said this was just syntactic sugar for capabilities we
>> already had?
> My mistake. I am sorry. I have to store somewhere flag. One bit, which
> signalise "don't use OUT arguments as function's parameters".
Huh? What exactly is the m
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> it can by more simple than I though. I need only one flag, and if its
true
> then I don't create language variables for OUT params. But I need one
next
> column in pg_proc.
I thought you said this was just syntactic sugar for capabilities we
alrea
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
>> Bruce Momjian <[EMAIL PROTECTED]> writes:
>>> I know it is kind of odd to have a data type that is only used on disk,
>>> and not in memory, but I see this as a baby varlena type, used only to
>>> store and get varl
"Jim Nasby" <[EMAIL PROTECTED]> writes:
> IF I run the following with the a < 2900 condition first, the more
> expensive EXISTS only gets executed when needed, but if I change the
> order of the OR's, the EXISTS is always executed. It would be good if
> the optimizer could re-order the OR con
Peter Eisentraut wrote:
Am Freitag, 9. Februar 2007 16:45 schrieb Andrew Dunstan:
. provide encode() for text values in addition to current bytea
. proved cast from text to bytea
I'm not sure how exactly you want to define these, but some possible
defintions will be dependent on the s
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> From recent IRC discussion:
> . provide encode() for text values in addition to current bytea
> . proved cast from text to bytea
What would these be meant to do? In particular I'm wondering if the
proposed cast is meant to be a binary-compatible trans
Am Freitag, 9. Februar 2007 16:45 schrieb Andrew Dunstan:
> . provide encode() for text values in addition to current bytea
> . proved cast from text to bytea
I'm not sure how exactly you want to define these, but some possible
defintions will be dependent on the server encoding, which sounds
tr
On Fri, 2007-02-09 at 10:17 -0500, Tom Lane wrote:
> Teodor Sigaev <[EMAIL PROTECTED]> writes:
> >> Implementing the "replace these TIDs" operation atomically would be
> >> simple, except for the new bitmap index am. It should be possible there
>
> > That isn't simple (may be, even possible) fro
From recent IRC discussion:
. provide encode() for text values in addition to current bytea
. proved cast from text to bytea
Thoughts?
cheers
andrew
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
Teodor Sigaev <[EMAIL PROTECTED]> writes:
>> Implementing the "replace these TIDs" operation atomically would be
>> simple, except for the new bitmap index am. It should be possible there
> That isn't simple (may be, even possible) from GIN.
I suspect that those pushing this idea only care abou
Implementing the "replace these TIDs" operation atomically would be
simple, except for the new bitmap index am. It should be possible there
That isn't simple (may be, even possible) from GIN.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
Doug Knight wrote:
I would also be interested in any "creative" ways to reduce the size and
time to backup databases/clusters. We were just having a conversation
about this yesterday. We were mulling over things like using rsync to
only backup files in the database directory tree that actually ch
On 2/8/2007 2:46 PM, Marc Munro wrote:
On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote:
Marc Munro <[EMAIL PROTECTED]> writes:
> Yes in this case, T1 must abort because the record it was going to
> update has disappeared from underneath it. I don't see how this is
> significantly different fr
Greg Stark <[EMAIL PROTECTED]> writes:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
>> I know it is kind of odd to have a data type that is only used on disk,
>> and not in memory, but I see this as a baby varlena type, used only to
>> store and get varlena values using less disk space.
> I was lea
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> it can by more simple than I though. I need only one flag, and if its true
> then I don't create language variables for OUT params. But I need one next
> column in pg_proc.
I thought you said this was just syntactic sugar for capabilities we
already
Pavel Stehule wrote:
> >OK, where are we on this patch?
>
> without changes. This task have to do anybody who better know PostgreSQL
> cache system than me.
How about you submit a version without any caching, but which works
correctly; and we worry about optimizations later?
> >---
On Fri, Feb 09, 2007 at 09:49:25AM -0500, Tom Lane wrote:
> "Takayuki Tsunakawa" <[EMAIL PROTECTED]> writes:
> > I wonder whether the field you are talking about set Windows to use
> > more memory for programs than for filesystem cache, which is
> > selectable from [System] applet of Control Panel
"Takayuki Tsunakawa" <[EMAIL PROTECTED]> writes:
> I wonder whether the field you are talking about set Windows to use
> more memory for programs than for filesystem cache, which is
> selectable from [System] applet of Control Panel (Oh, I wonder how my
> machine is set in this respect... have to c
On Fri, Feb 09, 2007 at 07:31:33PM +0900, Takayuki Tsunakawa wrote:
> 1. PostgreSQL tries to read data from disk into database cache.
> 2. The kernel tries to allocate filesystem buffers by paging out
> PostgreSQL's memory (possibly shared buffers).
> 3. PostgreSQL finds data requested by its clien
Oleg Bartunov wrote:
On Fri, 9 Feb 2007, Peter Eisentraut wrote:
Am Donnerstag, 8. Februar 2007 16:32 schrieb Bruce Momjian:
Log Message:
---
Add lock matrix to documentation.
This needs some revisions. The table needs to be mentioned somewhere
in the
text, so the reader knows whe
"Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]> writes:
> So when a RI check locks a parent, you would not be able to update the
> parent in a later subtrans. I can imagine, that the error would be a
> problem in a select for update loop, because there you usually want to
> update the row.
No, i
On 2/9/07, Oleg Bartunov wrote:
On Fri, 9 Feb 2007, Peter Eisentraut wrote:
> Am Donnerstag, 8. Februar 2007 16:32 schrieb Bruce Momjian:
>> Log Message:
>> ---
>> Add lock matrix to documentation.
>
> This needs some revisions. The table needs to be mentioned somewhere in the
> text,
Greg Stark wrote:
>
> Bruce Momjian <[EMAIL PROTECTED]> writes:
>
> > Bruce Momjian wrote:
> > >
> > > Uh, I thought the approach was to create type-specific in/out functions,
> > > and add casting so every time there were referenced, they would expand
> > > to a varlena structure in memory.
>
[EMAIL PROTECTED] wrote:
Hi Folks,
We have a requirement to deal with large databases of the size
Terabytes when we go into production. What is the best database
back-up mechanism and possible issues?
pg_dump can back-up database but the dump file is limited by OS
file-size limit. What abo
On Thu, 2007-02-08 at 20:49 -0500, Bruce Momjian wrote:
> Who is working on this item?
Jonah was trying to complete this for 8.3, but I believe that he has
handed it onto Gregory Stark - I think
http://archives.postgresql.org/pgsql-hackers/2007-01/msg01586.php is the
latest update.
Kind regards,
On Fri, Feb 09, 2007 at 01:00:10PM +0900, Koichi Suzuki wrote:
> Further, we can apply gzip to this archive (2.36GB). Final size is
> 0.75GB, less than one sixtieth of the original WAL.
Note that if you were compressing on the fly, you'll have to tell gzip
to regularly flush its buffers to make
Tom Lane wrote:
ISTM we could fix that by extending the index VACUUM interface to
include two concepts: aside from "remove these TIDs when you find them",
there could be "replace these TIDs with those TIDs when you find them".
This would allow pointer-swinging to one of the child tuples, after
wh
I would also be interested in any "creative" ways to reduce the size and
time to backup databases/clusters. We were just having a conversation
about this yesterday. We were mulling over things like using rsync to
only backup files in the database directory tree that actually changed.
Or maybe doing
[EMAIL PROTECTED] wrote:
We have a requirement to deal with large databases of the size Terabytes
when we go into production. What is the best database back-up mechanism
and possible issues?
It depends.
Make sure you read Chapter 23. Backup and Restore of the user manual:
http://www.postgres
Hello,
it can by more simple than I though. I need only one flag, and if its true
then I don't create language variables for OUT params. But I need one next
column in pg_proc.
Currently a lot of columns in pg_proc is bool. What about one binary columns
for other options? I hope so next versi
On Fri, 9 Feb 2007, Peter Eisentraut wrote:
Am Donnerstag, 8. Februar 2007 16:32 schrieb Bruce Momjian:
Log Message:
---
Add lock matrix to documentation.
This needs some revisions. The table needs to be mentioned somewhere in the
text, so the reader knows when or why to refer to it.
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Bruce Momjian wrote:
> >
> > Uh, I thought the approach was to create type-specific in/out functions,
> > and add casting so every time there were referenced, they would expand
> > to a varlena structure in memory.
Are you talking about actual casts?
On Thu, 2007-02-08 at 14:47 +, Heikki Linnakangas wrote:
> However, the easiest solution would be to make CREATE INDEX wait until
> the old tuple is dead. That should be ok at least for concurrent CREATE
> INDEX, because it already has that kind of a wait between 1st and 2nd
> phase.
I'm n
On 2/9/07, Simon Riggs <[EMAIL PROTECTED]> wrote:
On Wed, 2007-02-07 at 14:17 -0500, Tom Lane wrote:
> ISTM we could fix that by extending the index VACUUM interface to
> include two concepts: aside from "remove these TIDs when you find them",
> there could be "replace these TIDs with those TID
Further information about the following evaluation:
Pgbench throughput was as follows:
Full WAL archiving (full_page_writes=on), 48.3GB archive: 123TPS
Gzip WAL compress, 8.8GB archive: 145TPS
Physical log removal, 2.36GB archive: 148TPS
full_page_writes=off, 2.42GB archive: 161TPS
Koichi Suzuki
On Fri, Feb 09, 2007 at 01:06:13PM +0900, Takayuki Tsunakawa wrote:
> > On Thu, Feb 08, 2007 at 09:50:26PM +0900, Takayuki Tsunakawa wrote:
> >> When I try to start PostgreSQL 8.2.1 on Windows 2003 Server with
> >> shared_buffers=1024MB, I get the following error messages in the
> Event
> >> Log (w
On Feb 8, 2007, at 8:41 PM, Richard Troy wrote:
It'd be great if Jan considers the blending of replication; any
given DB
instance shouldn't be only a master/originator or only a slave/
subscriber.
A solution that lets you blend replication strategies in a single
db is,
from my point of view,
Bruce Momjian wrote:
Luke Lonergan wrote:
Yay!
This rocks IMO, but I'm a borderline PHB so what do I know ;-)
You think, "Oh, this will make my developers more productive". :-)
Well, I just had a university contact thank us for setting it up. So,
thanks!
--Josh Berkus
---
Richard Troy wrote:
It'd be great if Jan considers the blending of replication; any given DB
instance shouldn't be only a master/originator or only a slave/subscriber.
A solution that lets you blend replication strategies in a single db is,
from my point of view, very important.
Perhaps if mo
Hi Folks,
We have a requirement to deal with large databases of the size Terabytes
when we go into production. What is the best database back-up mechanism
and possible issues?
pg_dump can back-up database but the dump file is limited by OS file-size
limit. What about the option of compressing
Bruce Momjian wrote:
>
> Uh, I thought the approach was to create type-specific in/out functions,
> and add casting so every time there were referenced, they would expand
> to a varlena structure in memory.
Oh, one more thing. You are going to need to teach the code that walks
through a tuple at
On 2/7/2007 7:13 AM, José Orlando Pereira wrote:
On Saturday 03 February 2007, Bruce Momjian wrote:
Jan Wieck wrote:
> I don't have any such paper and the proof of concept will be the
> implementation of the system. I do however see enough resistance against
> this proposal to withdraw the commi
> Our evaluation result is as follows:
> Database size: 2GB
> WAL size (after 10hours pgbench run): 48.3GB
> gzipped size: 8.8GB
> removal of the physical log: 2.36GB
> fullpage_writes=off log size: 2.42GB
> I'm planning to do archive log size evalutation with other benchmarks
> such as DBT-2 as
On 2/8/2007 11:41 PM, Richard Troy wrote:
On Thu, 8 Feb 2007, Joshua D. Drake wrote:
Well how deep are we talking here? My understanding of what Jan wants to
do is simple.
Be able to declare which triggers are fired depending on the state of
the cluster.
In Jan's terms, the Origin or Subscrib
From: "Magnus Hagander" <[EMAIL PROTECTED]>
> Right. Which is why you're likely to see better performance if you
keep
> shared buffers smaller. There is something in dealing with it that's
> slow on win32, per reports from the field. It needs to be
investigated
> further...
> We've had reports that
Am Donnerstag, 8. Februar 2007 16:32 schrieb Bruce Momjian:
> Log Message:
> ---
> Add lock matrix to documentation.
This needs some revisions. The table needs to be mentioned somewhere in the
text, so the reader knows when or why to refer to it. Also, the cryptic
abbreviations need to
> As for what I think we *should* do near-term, I'm pretty strongly
> tempted to suggest that we just throw an error if a subtransaction
tries
> to upgrade an upper transaction's shared lock to exclusive.
So when a RI check locks a parent, you would not be able to update the
parent
in a later sub
Tom Lane wrote:
I'm testing the patch currently. I was a bit surprised to find the
without_oid test failing, but it makes sense because I'm using a
MAXALIGN=8 machine. I suppose Heikki tested on MAXALIGN=4.
That's right.
Thanks for the review!
--
Heikki Linnakangas
EnterpriseDB http:/
OK, where are we on this patch?
without changes. This task have to do anybody who better know PostgreSQL
cache system than me.
Regards
Pavel
---
Pavel Stehule wrote:
>
>
> >
> >"Pavel Stehule" <[EMAIL PROTECTED]> wr
81 matches
Mail list logo
