[SQL] Having elements of an int[]-array reference other tables
Hi. Is it possible in PG to have elements in an int[]-array reference another int-column in another table? What I want is it to behave as a foreign key. create table master( my_id integer primary key ); create table test( id serial primary key, id_array integer[] references master(my_id) ); It would be nice if one could have elements in test.id_array to reference elements in master.my_id, with all the benefits of ON DELETE | UPDATE etc. I know I can accomplish this with triggers, but if there exists something built-in I'd like to know. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Having elements of an int[]-array reference other tables
Hello, it isn't possible without custom triggers. regards Pavel Stehule On 21/01/2008, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > Hi. > Is it possible in PG to have elements in an int[]-array reference another > int-column in another table? What I want is it to behave as a foreign key. > > create table master( > my_id integer primary key > ); > > create table test( > id serial primary key, > id_array integer[] references master(my_id) > ); > > It would be nice if one could have elements in test.id_array to reference > elements in master.my_id, with all the benefits of ON DELETE | UPDATE etc. > > I know I can accomplish this with triggers, but if there exists something > built-in I'd like to know. > > -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > Senior Software Developer / Manager > +-+ > OfficeNet AS| The most difficult thing in the world is to | > Karenslyst Allé 11 | know how to do a thing and to watch | > PO. Box 529 Skøyen | somebody else doing it wrong, without | > 0214 Oslo | comment.| > NORWAY | | > Tlf:+47 24 15 38 90 | | > Fax:+47 24 15 38 91 | | > Mobile: +47 909 56 963 | | > +-+ > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] transaction and triggers
D'Arcy J.M. Cain wrote: On Fri, 18 Jan 2008 12:16:04 -0300 Gerardo Herzig <[EMAIL PROTECTED]> wrote: Right. But today, that trigger do some other work, wich includes writing some files to disk, so there is my problem. Crap, i guess i will have to review the main logic. I built a replication system that syncs up dozens of systems in a multi-master environment spanning multiple continents in almost real-time and it works flawlessly so don't give up hope. And im trying with 3 virtual machines...this is embarrasing :) It is doable. I can't give you the code because it was written under contract and it was based heavily on our specific business requirements but I can give you a few pointers. You have discovered the basic problem of trying to replicate in full real time. You'll probably have to give up on that. Instead, focus on making updates to the local database. Create a replication table or tables that you update with triggers. Basically this needs to be a log of every change to the database in a structured way. Crap. That was my first approach! I later chose the inmediate file writing, trying to minimize the changes that would be lost in case of primary system crash. I guess i will come with it again. Once you have the replication table(s) you can create external programs that connect to the master and update the slave. In the slave you can track the last ID that completed. Do the insert/update/delete in a transaction so that you have a guarantee that your database is up to date to a very specific point. Note that you can have multiple slaves in this scenario and, in fact, the slaves can have slaves using the exact same scheme giving you a hierarchy. If you need multi-master you just need to have another process to feed your local changes up to the master. This is not just a matter of making the master a slave though. If you do that you get into a feedback loop. Also, if you need multi-master, you have to think about your sequencing. If you need unique IDs on some tables you will have to think about setting up ranges of sequences based on server or have a central sequence server. We used a combination of both as well as specifying that certain tables could only be inserted to on one system. Of course, this system doesn't need to be the same as the top of the hierarchy and, in fact, different tables can have different generator systems. What i want to do is something like: If the master fails, it will be a peace of soft that would change the conf files (which indicate who's the master, slaves, and so on), so one of the slaves take the master's place. Since those are a common pc, when the real master come back to life, it has to be re-sync, and take his place as the master again. Im thinking in something as simple as posible (since im not a senior programmer), something like a ip address change could do the trick Hope this gets you started. There's still lots of gotchas on the way. Oh yes, im specting so much fun Thanks for sharing your knowledge with us!! Mamooth replicator, Slone-I, feel the fear! :) Thanks again, D'arcy! Gerardo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] (possible) bug with constraint exclusion
Hi list, It's a bug, it's patched: http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php I have just stumbled on the same bug today and was very happy to find a patch; however, I have a 8.2.6 server running which of course cannot be patched. (According to the CVS tags the revisions of plancat.c and predtest.c in the 8.2.6 release are 1.127 and 1.10.2.2, respectively, whereas the patch is against the head revisions.) Would it be possible to provide a patch against the 8.2.6 stable release (the one that can be downloaded from the download section of the homepage)? It seems as if partitioning is unusable without this patch because when constraint exclusion is enabled, records cannot be found by select or update even in tables where partitioning is not realized at all. On the other hand, without constraint exclusion, partitioning at all doesn't make sense (as far as I understand). Thanks a lot for your help! Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] (possible) bug with constraint exclusion
=?ISO-8859-1?Q?Christian_Schr=F6der?= <[EMAIL PROTECTED]> writes: >> It's a bug, it's patched: >> http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php >> > I have just stumbled on the same bug today and was very happy to find a > patch; however, I have a 8.2.6 server running which of course cannot be > patched. (According to the CVS tags the revisions of plancat.c and > predtest.c in the 8.2.6 release are 1.127 and 1.10.2.2, respectively, > whereas the patch is against the head revisions.) Better look again. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] (possible) bug with constraint exclusion
Tom Lane wrote: Better look again. Sounds like a sensible advice ... I somehow managed to find http://archives.postgresql.org/pgsql-committers/2008-01/msg00183.php instead of http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php ... Sorry for that! Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] improvements to query with hierarchical elements
Date: Sun, 20 Jan 2008 20:01:08 -0800 From: Ryan Wallace <[EMAIL PROTECTED]> To: [email protected] Subject: improvements to query with hierarchical elements Message-ID: <[EMAIL PROTECTED]> Greetings, I have a complex query which I am trying to figure out the most efficient way of performing. My database is laid out as follows: items -have_many-> events -have_many-> event_locations -have_many-> locations also rows in the location_links table link two locations together in a parent-child relationship and rows in the location_descendants table provide a full list of the descendants of a particular location. I am trying to find all locations which both are direct children of a given parent location, and are associated with at least one item in a constrained subset of items. (eg. Find all states of the USA in which at least one wooden axe was made. Also find the number of wooden axes made in each state.) I have developed the following query: SELECT locations.*, location_ids.item_count AS item_count FROMlocations JOIN (SELECT immediate_descendants.ancestor_id AS id, COUNT(DISTINCT creation_events.item_id) AS item_count FROMevent_locations JOIN (SELECT * FROMlocation_descendants WHERE ancestor_id IN (SELECT child_id FROMlocation_links WHERE parent_id = *note 1* ) ) AS immediate_descendants ON event_locations.location_id = immediate_descendants.descendant_id JOIN (SELECT * FROMevents WHERE item_id IN (*note 2*) AND association = 'creation' ) AS creation_events ON event_locations.event_id = creation_events.id GROUP BY immediate_descendants.ancestor_id ) AS location_ids ON locations.id = location_ids.id *note 1* - the id of the parent location. *note 2* - the query which returns a list of constrained item ids This works but I am looking for any way to improve the performance of the query (including changing the layout of the tables). Any ideas, suggestions or general pointers would be greatly appreciated. Thanks very much, Ryan Hi Ryan, I have built some similar queries so I might be able to help you. But it's a little hard (for me) to dig into your query without a test set. Could you please post some create table and insert statements to give us a little test bed to run your query in? I realize that may be a fair bit of work for you but it would help me to give you some ideas. Without seeing a more formal schema and being able to toy with it, I'm not sure I can give good advice. Others may have different opinions which I would welcome. Sincerely, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] currval() within one statement
Helo
is it expected that the currval() changes its value between calls within
one statement ?
Look the following call:
INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2;
Where the trigger before insert on ttt is defined and this trigger calls
nextval('ttt_id_seq').
I was surprised having different values of currval() in ttt.a
Is this the normal behavior ? Where is it described ?
---(end of broadcast)---
TIP 1: 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: [SQL] currval() within one statement
A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to be given an exact pointer to documentation regarding the currval() behavior in the described situation, that i had. ---(end of broadcast)--- TIP 1: 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: [SQL] currval() within one statement
am Tue, dem 22.01.2008, um 10:16:30 +0300 mailte silly_sad folgendes:
> Helo
>
> is it expected that the currval() changes its value between calls within
> one statement ?
>
> Look the following call:
>
> INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2;
This fails if you never call nextval() for this sequence within this
session.
>
> Where the trigger before insert on ttt is defined and this trigger calls
> nextval('ttt_id_seq').
You don't need a TRIGGER. Just define your table with (a serial, ...)
and omit the column a if you INSERT a new row.
> I was surprised having different values of currval() in ttt.a
If you call nextval() befor the insert, then returns the currval(), for
instance, 5. If you call your insert with the TRIGGER like above, the
currval() returns this value 5, but your trigger fires and increase the
value. And, maybe, an other process has increased the sequence also.
Conclusion, don't call nextval() within a TRIGGER, and insert either
nextval() for the column or omit this column.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
