Re: [SQL] Temp tables and functions
Thanks, Jim. I'll give it a try. On Tue, 2006-10-10 at 21:11 -0500, Jim C. Nasby wrote: > On Tue, Oct 10, 2006 at 03:21:36PM -0400, [EMAIL PROTECTED] wrote: > > Everyone, > > > > I've written a function (language = sql) which uses a temporary table to > > simplify the process; however, when I go to load the function I get: > > > > /var/lib/pgsql$cat thm.sql | psql test > > ERROR: relation "lost_bus" does not exist > > > > where "lost_bus" is the name of my temp table. The function is just for a > > report that is run monthly, so I can create a permanent table if necessary, > > but I'd rather not. > > Create the temp table in your script that creates the function. You > don't need to populate it or anything, you just need it to exist > (prefferably with the correct definition). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] optimal insert
Hi, Dirk, Dirk Jagdmann wrote: > So for a batch of inserts the value of a stays the same, while for by > arbitrary values are inserted. Now I have wondered if PostreSQL offers > a smarter way to insert those values? A solution can contains usage of > some plpgsql code. [local]:postgres=# insert into a_b (a,b) SELECT 1, (ARRAY[100,200,54,4577])[i] FROM generate_series(1,4) AS i; INSERT 0 4 [local]:postgres=# select * from a_b; a | b ---+-- 1 | 100 1 | 200 1 | 54 1 | 4577 But I tend to agree with others that packing a whole bunch of INSERTs into a single transaction, and using COPY instead will give you better benefits. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] deleting rows in specific order
Hi, Daniel, Daniel Drotos wrote: > What is the best way to do something like: > > delete from tablename where something order by somefield... There should be no need for this, because a delete is atomic due to the transaction system. What is it that you're trying to achieve with the ordered delete? Maybe there's a better way to solve your underlying problem. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] deleting rows in specific order
On Wed, 11 Oct 2006, Markus Schaber wrote: What is it that you're trying to achieve with the ordered delete? Maybe there's a better way to solve your underlying problem. I've a table which has a field for some info (number) and a date field (insertion date) and others. After inserting new rows into this table, very old rows are going to be deleted. But value of deleted info should be remembered somehow, so there is a trigger on delete which keeps sum of the deleted info values in a separate table. This makes the application happy. Trigger's algorithm is a little bit more dificult which I would be able to simplify if rows are deleted in date order. Now I'm doing it in a plpgsql function using a `for in select loop' to delete rows one by one. Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] slow view
I am having a problem with the performance of a
view that will be a critical part of a database system
I am working on, and would appreciate some advice.
Apologies for the length of this posting!
I have a parent table P and to child tables A and B,:
CREATE TABLE p (
id INT NOT NULL PRIMARY KEY);
CREATE TABLE a (
id INT NOT NULL PRIMARY KEY,
p INT REFERENCES p(id));
CREATE INDEX ON a(p);
CREATE TABLE b (
id INT NOT NULL PRIMARY KEY,
p INT REFERENCES p(id));
CREATE INDEX ON b(p);
Each "p" row has between 1 and 5 (or so)
child rows in the "a" table, and between 0 and 4
rows in the "b" table.
Now for most p's the a's and the b's are independent,
and all combinations of a's and b's for that p are ok.
But for a small percentage of p's (<5%) there are some
combinations of a's and b's are distinguished (I will call
them "invalid").
So I created a table to record these "invalid" combinations:
CREATE TABLE x (
a INT NOT NULL REFERENCES a(id),
b INT NOT NULL REFERENCES b(id),
PRIMARY KEY (a,b));
CREATE INDEX ON x(a);
CREATE INDEX ON x(b);
Here is some sample data with a single p:
# Create one parent item...
INSERT INTO p VALUES(1)
# Create 4 a-items for that parent...
INSERT INTO a VALUES(1,1)
INSERT INTO a VALUES(2,1)
INSERT INTO a VALUES(3,1)
INSERT INTO a VALUES(4,1)
# Create 3 b-items for that parent...
INSERT INTO b VALUES(11,1)
INSERT INTO b VALUES(12,1)
INSERT INTO b VALUES(13,1)
So for parent p=1, there are 12 combinations
of a and b items (each of the 4 a items can be
paired with any of the 3 b items).
Now, make some combinations of a items
and b items "invalid"...
# For a=2, make b=13 invalid, i.e only b=11 and b=12 are valid.
INSERT INTO x VALUES(2,13)
# For a=3, only b=11 is valid.
INSERT INTO x VALUES(3,12)
INSERT INTO x VALUES(3,13)
# For a=4, no b's are valid.
INSERT INTO x VALUES(4,11)
INSERT INTO x VALUES(4,12)
INSERT INTO p VALUES(4,13)
Now I need a view that will display, for each p, its
a's and for each a, only the valid b's, that is, the
combinations of a and b that are *not* in table x.
OK, no problem...
(#1)
SELECT p.id AS pid, a.id AS aid, b.id AS bid
FROM p
JOIN a ON a.p=p.id
LEFT JOIN b ON b.p=a.p
LEFT JOIN x ON x.a=a.id AND x.b=b.id
WHERE x.a IS NULL
AND p.id=1;
Here is the result on the data given above:
pid | aid | bid
-+-+-
1 | 1 | 11
1 | 1 | 12
1 | 1 | 13
1 | 2 | 11
1 | 2 | 13
1 | 3 | 11
1 | 3 | 12
Ok, but I want all a's in the output, even when they
have no valid b's. aid=4 is not in the output.
So I did what I thought was the obvious answer, a
left join between a, and the above query...
(#2)
SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
FROM p
JOIN a ON a.p=p.id
LEFT JOIN (
SELECT a.id AS aid, b.id as bid
FROM a
LEFT JOIN b ON b.p=a.p
LEFT JOIN x ON x.a=a.id AND x.b=b.id
WHERE x.a IS NULL
) AS sub ON sub.aid=a.id
WHERE p.id=1;
Results:
pid | aid | bid
-+-+-
1 | 1 | 11
1 | 1 | 12
1 | 1 | 13
1 | 2 | 11
1 | 2 | 13
1 | 3 | 11
1 | 3 | 12
1 | 4 |
Exactly what I want.
The problem is that when there are ~100K parent entries
the above query (#2) takes ~10 seconds to run but the first
query (#1) runs in a few tens of milliseconds.
Is there any way I can get postgresql to better optimize
query #2, or rewrite it to that is is more "postgresql friendly"?
~~~
If it helps, here is a python script I used to generate enough
pseudo-data to show the time difference...
#!/usr/bin/python
import psycopg2, random
def main():
cn = psycopg2.connect (database="test",
user="postgres",password="")
c = cn.cursor()
pkp = 1; pka = 1; pkb = 1;
while pkp < 3:
c.execute ("INSERT INTO p VALUES(%s)", (pkp,))
na = random.randint(1,5)
for a in range(na):
c.execute ("INSERT INTO a VALUES(%s,%s)", (pka+a,pkp))
nb = random.randint(0,4)
for b in range(nb):
c.execute ("INSERT INTO b VALUES(%s,%s)", (pkb+b,pkp))
if na*nb > 1 and random.randint (0,99) < 10:
zlst = [(a,b) for a in range(pka,pka+na)
for b in range(pkb,pkb+nb)]
for z in random.sample (zlst, random.randint(1,na*nb-1)):
c.execute ("INSERT INTO x VALUES(%s,%s)", z)
pkp += 1; pka += na; pkb += nb
cn.commit()
if __name__ == '__main__': main ()
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] slow view
On 2006/10/11 Stuart wrote: > [..] Apologies for following up my own post, but after struggling with that query for over a day, I figured out the answer within thirty minutes of posting. (Sigh) My slow query was: > SELECT p.id AS pid, a.id AS aid, sub.bid AS bid > FROM p > JOIN a ON a.p=p.id > LEFT JOIN ( > SELECT a.id AS aid, b.id as bid > FROM a > LEFT JOIN b ON b.p=a.p > LEFT JOIN x ON x.a=a.id AND x.b=b.id > WHERE x.a IS NULL > ) AS sub ON sub.aid=a.id > WHERE p.id=1; Including the parent table p in the inner select was all it took. SELECT p.id AS pid, a.id AS aid, sub.bid AS bid FROM p JOIN a ON a.p=p.id LEFT JOIN ( SELECT p.id AS pid, a.id AS aid, b.id as bid FROM p JOIN a ON a.p=p.id LEFT JOIN b ON b.p=a.p LEFT JOIN x ON x.a=a.id AND x.b=b.id WHERE x.a IS NULL ) AS sub ON sub.aid=a.id AND sub.pid=p.id WHERE p.id=1; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Is this a bug? Deleting a column deletes the constraint.
I got bit by this to be sure, but is it a bug? I guess I'd hoped for a warning at the critical step (see "poof" below): create table tester (one int, two int, three int); alter table only tester add constraint no_dupes unique (one, two, three); insert into tester values(1,2,3); insert into tester values(1,2,4); insert into tester values(1,2,3); -- this gets denied by the constraint alter table tester add column new_three varchar(8); alter table tester rename column three to old_three; \d tester;-- the constraint moves to the new column name alter table tester rename column new_three to three; \d tester;-- the constraint remains alter table tester drop column old_three; \d tester;-- poof, the constraint is gone with no warning -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is this a bug? Deleting a column deletes the constraint.
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > I got bit by this to be sure, but is it a bug? I guess I'd hoped for a > warning at the critical step (see "poof" below): > create table tester (one int, two int, three int); > alter table only tester add constraint no_dupes unique (one, two, three); > ... > alter table tester drop column old_three; > \d tester;-- poof, the constraint is > gone with no warning I don't think the renaming is relevant: you get the same with regression=# create table tester (one int, two int, three int, unique (one,two,three)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "tester_one_key" for table "tester" CREATE TABLE regression=# alter table tester drop column three; ALTER TABLE regression=# \d tester Table "public.tester" Column | Type | Modifiers +-+--- one| integer | two| integer | regression=# It does seem like this is wrong, in view of SQL92's statement about ALTER TABLE DROP COLUMN: 4) If RESTRICT is specified, then C shall not be referenced in the of any view descriptor or in the of any constraint descriptor other than a table con- straint descriptor that contains references to no other column and that is included in the table descriptor of T. IOW we should only allow unique constraints to be auto-dropped if they reference just the one single column. Ick. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
