Re: [ADMIN] [GENERAL] Storing database in WORM devices
On Wed, May 11, 2005 at 03:51:43PM -0400, [EMAIL PROTECTED] scratched on the
wall:
> I would like to clarify something.
> I intend to create the database on a re-writable device (not WORM).
> At some point, when I no longer want to add/modify the database, I
> plan to copy it to the WORM device. Then I would like to be able to
> access the database on the WORM device for reading purposes only.
What you are basically saying is you want a read-only database on a
CD-R (which is essentially a WORM device). As has been discussed
many times in the past, Postgres does not support this very well, if
at all.
-j
--
Jay A. Kreibich | CommTech, Emrg Net Tech Svcs
[EMAIL PROTECTED] | Campus IT & Edu Svcs
<http://www.uiuc.edu/~jak> | University of Illinois at U/C
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] Recursive use
On Wed, Oct 04, 2006 at 10:08:10AM -0300, Alexander Burbello scratched on the wall: > Hi people, > > I need to know if Postgres do recursive search and how can I do! > I will explain my problem. > > > table COOPERATIVE > code_cooperative int > code_coo_father int > > I can have 3 level by business rules > > 1 - Father > - 2 - Children > - 3 - Grandchildren > > > I would like to have a query asking who is father and granfather > select grandfather, father from COOPERATIVE where COD_COOPERATIVE = 3 > > Do the Postgres can solve this problem? > Could anybody help me? These are generally referred to as "Hierarchical Queries" and center around the idea of a self-referencing table (such as an employee table with a "manager" field that is a FK to another row in the same table). This essentially makes a tree-like structure. Oracle supports these types of queries with their "START WITH ... CONNECT BY" extensions to SELECT. In Oracle, hierarchical queries also return a pseudo-column called "LEVEL" that is the depth of a node in the tree. The syntax is fairly complete and allows all kinds of queries up and down the tree. It is extremely useful for dealing with self-referencing tables. Alas, PostgreSQL does not support a similar set of extensions. Although self-referencing tables are a bit of a design niche, they show up all then time when translating traditional computer memory structures and object trees into RDBMS storage systems. It would be really cool if "START WITH ... CONNECT BY" or some similar set of extensions was found in PostgreSQL. As pointed out by others, the most general way to deal with this in PostgreSQL is to write PL/PgSQL (or some other language) functions that can generate the specific queries you need. It isn't always pretty, but it can be made to work for a specific set of queries. If you have a known structure (like the fact that your tree is never any more than three levels deep) you can also join the table to itself multiple times. This can get really confusing very quickly, and is not an overly general solution, but it can be done in "pure" SQL in a fairly straight forward (if not a bit complex) kind of way. -j -- Jay A. Kreibich | CommTech, Emrg Net Tech Svcs [EMAIL PROTECTED] | Campus IT & Edu Svcs <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Recursive use
On Tue, Oct 10, 2006 at 10:15:42AM -0500, Jim C. Nasby scratched on the wall:
> On Fri, Oct 06, 2006 at 10:37:26AM -0500, Jay A. Kreibich wrote:
> > These are generally referred to as "Hierarchical Queries" and center
> > around the idea of a self-referencing table (such as an employee
> > table with a "manager" field that is a FK to another row in the same
> > table). This essentially makes a tree-like structure.
>
> > As pointed out by others, the most general way to deal with this in
> > PostgreSQL is to write PL/PgSQL (or some other language) functions
> > that can generate the specific queries you need. It isn't always
> > pretty, but it can be made to work for a specific set of queries.
>
> There are also other ways to represent this type of information without
> using hierarchical queries. Joe Celko presents two methods in SQL For
> Smarties.
If you're referring to Joe's March 1996 DBMS article,
(http://www.dbmsmag.com/9603d06.html) he does demonstrate two models,
but one of them is the self-referencing table model where one column
references another column in the same table. His only suggestion for
dealing with these kinds of tables is self-joins (which I also
mentioned) but points out the obvious limitation that-- unless you go
procedural-- you have to know how many levels you're going to process
before you setup the query.
The other model that is shown (which he calls "nested-set") is
interesting, but has a lot of properties that make me uncomfortable.
(He proposes each node/row have two sequence counters ("left" and "right")
represent pre- and post-visit order in a depth-first traversal; sets
can be calculated by differences or betweens of the two values).
For one, the table requires an extreme amount of maintenance-- something
as simple as inserting a single leaf node may require updating every
row in the whole table. On average, more than half the nodes/rows will
require updating for each record insertion and removal, but it isn't clear
how this update process would work (since the sequences require a
traversal to update, but a proper traversal requires the correct
sequences). There are tricks for the simple cases, but I'm not sure
you could do an update in-place in the general case.
The representation he's chosen also introduces an ordering among siblings--
while this is a required attribute of some tree structures, in most
cases (and in the spirit of general SQL sets) the ordering of peer
nodes/rows is undefined and unimportant. This isn't exactly a flaw,
so much as an unexpected side-effect.
In theory, I agree with his assertion that a conceptual "nested sets"
approach is more SQLish (since SQL likes to deal with sets), but I don't
think the implementation he presented actually has anything to do with
sets (in the traditional sense) that are nested. The whole thing depends
on understanding traversal orderings and some of the tricks you can play
with that to indirectly define sets. I guess it all depends on how you
look at it. I personally tend to think more in C++ than SQL anyways.
I also noticed that Joe has a book out titled "Joe Celko's Trees and
Hierarchies in SQL for Smarties". I have not yet had a chance to
review this book (other than the on-line table of contents) but it
looks interesting. While much of this is on graphs and more general
edge/node structures, a fair bit of the book appears to be about this
type of tree structure. He goes into more detail on some of these
issues, such as insertion and deletion times, and tricks to play for
inserting whole sub-trees, and that kind of thing. Maybe the book
would sell the so-called "nested-set" implementation a bit better,
but it still strikes me as a solution for warehouses, not OLTP style
stuff. I might have to find this book and have a closer read.
Thanks for the reference.
> There's also the ltree module in contrib that might be of some use.
Interesting.
-j
--
Jay A. Kreibich | CommTech, Emrg Net Tech Svcs
[EMAIL PROTECTED] | Campus IT & Edu Svcs
<http://www.uiuc.edu/~jak> | University of Illinois at U/C
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] Time is off in PG server
On Thu, Aug 26, 2004 at 09:47:26AM -0400, Ericson Smith scratched on the wall:
> Hi,
>
> When using date oriented functions on Postgresql, the time is an hour
> off, or in certain times, one hour ahead.
>
> System Timezone: EST
^^^
> System Time (date command): Thu Aug 26 09:44:28 EDT 2004
^^^
> SELECT now(); : 2004-08-26 08:44:31.307343-05
> SELECT date_part('epoch', '2004-08-26'::timestamp) ; : 1093496400 (1am
> on that day -- should be 12pm)
>
> Any suggestions?
Work in the same timezone. EST and EDT are not the same.
-j
--
Jay A. Kreibich | Integration & Software Eng.
[EMAIL PROTECTED] | Campus IT & Edu. Svcs.
<http://www.uiuc.edu/~jak> | University of Illinois at U/C
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [ADMIN] Multiple WALs for PITR ?
On Thu, Oct 07, 2004 at 10:32:04AM -0400, Goulet, Dick scratched on the wall: > Some disk array manufacturers had a problem with their writes in that > although the write the data was being stored in volatile memory instead > of on disk. The disk buffers were volatile in that if power suddenly > died all writes in memory that had not made it to disk were lost. > Lately most array manufacturers (at least those I have experience with > namely EMC & NetApp) have internal battery backup systems in place so > that in the event of a power failure the array will accept no new data, > but has sufficient battery power available to flush memory to a disk > drive cleanly before crashing. All of the RAID systems I've dealt with work a little differently. In most cases the internal array battery is only there to keep the contents of the buffer alive-- there is not enough power to actually keep a disk spinning and write the data out. You'd need a real UPS for that. Typically the internal batteries have enough power to keep the volatile memory contents active for two weeks or more. Once power is re-applied to the array, the buffers are automatically flushed by the RAID controller. They are also, frankly, a pain in the butt. We've had most of our batteries fail within 18 months or so, and replacements are often extremely expensive. For most of our applications we've just disabled the RAID controller write-caches (our application writes data at a very very slow rate; the main reason we have RAIDs is protection, size, and *read* speed). -j -- Jay A. Kreibich | Comm. Technologies, R&D [EMAIL PROTECTED] | Campus IT & Edu. Svcs. <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] NIC to NIC connection
On Tue, Oct 19, 2004 at 11:02:31AM -0600, Scott Marlowe scratched on the wall: > This is more a networking issue than a PostgreSQL one. What you'll like > want to do is to set up a nic in each box and use a "rolled" cable > directly between them Actually, you want a "cross-over" cable, not a "roll" cable. There is a difference. With many newer, high quality NICs, you don't even need that. Many modern NICs do auto MDI/MDI-X detection, so any standard cable will do. -j -- Jay A. Kreibich | Comm. Technologies, R&D [EMAIL PROTECTED] | Campus IT & Edu. Svcs. <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] NIC to NIC connection
On Thu, Oct 21, 2004 at 10:07:33AM +0200, Gaetano Mendola scratched on the wall: > Bruno Wolff III wrote: > >Also I believe that if > >a switch doesn't remember where a particular mac address is it will send > >the packet to all of the attached ports. > > I don't think so, I guess the switch perform a sort of arpping in order to > detect who have a macaddress assigned, No, he's right. If the MAC to port mapping has not been learned by the switch, the packet is flooded to all ports or (for really bad switches) dropped. A switch is a pure layer-two device and ARP involves layer-three addresses and concepts. The only part of a switch that will ARP is the management system, assuming it has an IP address of its own. This is actually a standard test we run when evaluating switches for deployment on campus. We hook about ten devices up to a switch and send them tons of traffic. The systems count incoming packets but they do not generate any traffic, so the switch is forced to flood all the traffic. The vast majority of low-cost switches roll over and die at that point. > even the multicast is not sent > to all ports but only to that ports where "someone" sent an arp packet > saying the he was registered to a multicast address. Not ARP, IGMP (Internet Group Management Protocol). It's part of the IP suite, and is much higher up in the protocol stack. And again, this isn't true unless the switch does IGMP snooping or has multicast assist from the router. IGMP snooping is becoming more common on higher-end switches, but there are some companies that prefer the router assist design. The problem with that is that the switches and routers need to be from the same company. IGMP snooping is also really tricky to do right, and there are still some situations where you are forced to flood traffic. -j -- Jay A. Kreibich | Comm. Technologies, R&D [EMAIL PROTECTED] | Campus IT & Edu. Svcs. <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] NIC to NIC connection
On Thu, Oct 21, 2004 at 07:05:40PM +0200, Gaetano Mendola scratched on the wall: > We have some switches that are able to do ip routing too... :-( So called "layer-three switches" are a whole different game. Essentualy, they are just routers where the "routing" interfaces and the physical interfaces are abstracted from each other with a layer-two switching engine. The term "layer-three swtich" is really more of a marketing term, however, and is not a very accurate description of what the device is or how it works. > I have to admint that I'm not a switch specialist but given the ability > to do routing I was imagine the arpping trich... Yes, but such systems are generally routers first and switches second. -j -- Jay A. Kreibich | Comm. Technologies, R&D [EMAIL PROTECTED] | Campus IT & Edu. Svcs. <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Backup
> Is there anybody using this for large DB's? Anything larger than a > couple hundred GB, it would be impractical to make full backups > everyday. Anyone out there using this for large DB's, if so I'd love to > hear how they are dealing with backups. You might want to have a look at this: http://www.postgresql.org/docs/8.0/static/backup-online.html -j -- Jay A. Kreibich | CommTech, Emrg Net Tech Svcs [EMAIL PROTECTED] | Campus IT & Edu Svcs <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
