Re: [SQL] double linked list
are you joe celko, guy who wrote those sql books?
"--CELKO--" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> >> The table at hand is more a kind of a collection of graphs where I
> want to find all possible paths between a given starting point and a
> given end point. <<
>
> For the reachabiity index of a general graph, you need Warshal's
> algorithm.
>
> Let V = number of nodes in the graph
> Let A[i,j] be the adjacency matrix for the undirected graph
>
> FOR j:= 1 TO V
> DO FOR i:= 1 TO V
> DO IF A[i,j] = 1
> THEN FOR k := 1 TO V
> DO IF A[j,k]] = 1
> THEN A[i,k]] := 1;
>
> You can also do a summation to get the length of the path from i to j.
> You can concatenate names of the nodes into a string that gives the
> path, etc.
>
> Her is a first attempt at some SQL; I am sure it can be done better
>
> CREATE TABLE Graph
> (i CHAR(2) NOT NULL,
> j CHAR(2) NOT NULL,
> flag CHAR(1) NOT NULL DEFAULT 'n'
>CHECK (flag IN ('n', 'y')),
> PRIMARY KEY (i,j));
>
> INSERT INTO Graph (i, j, flag)
> SELECT DISTINCT G1.i, G2.j, 'y'
>FROM Graph AS G1, Graph AS G1
> WHERE G1.i <> G2.j
> AND EXISTS
> (SELECT *
>FROM Graph AS G3
> WHERE G3.i = G1.j
> AND G3.j = G2.i)
> AND NOT EXISTS
> (SELECT *
>FROM Graph AS G3
> WHERE (G3.i = G1.i AND G3.j = G1.j))
> OR (G3.i = G2.i AND G3.j = G2.j));
>
> You wll have to run this statement until the size of Graph does not
> change -- no new rows are being added.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] aggregate question
I know this one is simple enough.
I have two tables: packages and package_log.
paulsonsoft=# \d packages
Table "public.packages"
Column | Type |Modifiers
-+-+--
package_name| text| not null
package_desc| text| not null
package_ver | text| not null
package_date| text| not null
package_loc | text| not null
package_type| text| not null
package_creator | text|
package_status | boolean | default true
package_id | integer | not null default
nextval('public.packages_package_id_seq'::text)
Indexes: packages_pkey primary key btree (package_id)
Foreign Key constraints: $1 FOREIGN KEY (package_type) REFERENCES
package_types(package_type) ON UPDATE CASCADE ON DELETE RESTRICT
paulsonsoft=# \d package_log
Table "public.package_log"
Column| Type |Modifiers
-+-+-
custno | text| not null
package_id | text|
timestamp | text| not null
ip_address | text| not null
completed | boolean |
current_ver | text|
logo| text|
licenses| text|
log_id | integer | not null default
nextval('public.package_log_log_id_seq'::text)
Foreign Key constraints: $1 FOREIGN KEY (package_id) REFERENCES
packages(package_id) ON UPDATE CASCADE ON DELETE RESTRICT
I must be a total space case today because I can't hammer out the sql to
get a listing of all the packages with a count() of the package_log by
package_id.
Thanks,
Ryan
---(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: [SQL] weird structure
Does it have to be in one row? Otherwise, assuming that person_data.d_person_id references person.a_id and person_data.d_attribute_id references person_attribute.a_id: select a.a_name from person p, person_data d, person_attribute a where p.p_name = 'UserYou'reLookingFor' AND p.p_id = d.d_person_id AND d.d_attribute_id = a.a_id Would return a list of attributes the person has, one per row. - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 24, 2000 11:24 AM Subject: [SQL] weird structure > Hi, > > Consider the following tables/fields: > table "person": fields "p_id", "p_name". > table "person_attribute": fields "a_id", "a_name". > table "person_data": fields "d_person_id", "d_attribute_id", > "d_value". > > Also consider that a person may not have data related to all possible > attributes. > > Using this structure, how could I retrieve in one row with a single > select statement all attributes from a person (showing null to > attributes that were not registered to him)? > > Thanks for any suggestion, > -- > Renato > Sao Paulo - SP - Brasil > [EMAIL PROTECTED] >
Re: [SQL] Viewing a function
Try "SELECT prosrc FROM pg_proc WHERE proname = 'funcname'", where funcname is the name of the function you want to see. - Original Message - From: "stuart" <[EMAIL PROTECTED]> To: "PG-SQL" <[EMAIL PROTECTED]> Sent: Tuesday, August 29, 2000 3:20 AM Subject: Fw: [SQL] Viewing a function > > -Original Message- > From: Stuart Foster <[EMAIL PROTECTED]> > To: PG-SQL <[EMAIL PROTECTED]> > Date: Wednesday, 30 August 2000 2:25 > Subject: [SQL] Viewing a function > > Helllo Stuart, > > Good question. > I have been fiddly with a function editor using zeos controls and I have > lots of little problems, so that I have given up for a while. > > I successfully parsed function information. (The following is from memory > and I dont have postgres with me right now). > The body of the function is in pg_lang.prosrc (I think) or something like > that > (I found out most stuff by looking in the c code for psql. I work in > delphi) > You can work out param numbers from another field and work out the field > types > (I confess Icheated here. You can get field type out of system tables but I > never actually worked out how). > > I could reconstruct a nice looking create function string using data in the > data base. > When I tried it execute things from windows things went wrong but it work in > psql. > I ended up just using it as a utility to create separated create function > files wich I then loaded server side. > > Anyway... then problems. > I dont think I ever had a successful update from client side. > Once I committed a change I often could not successfully drop and create a > function and vacuum would fail. > To recover I had to drop all functions and re-create them. > Using PERFORM seemed to cause particular problems. (Think that anything > that is called by perform must be compiled in before anything that calls it. > Order of creation seems to important. A full dump and create would fix > these sort of problems) > The cr/lf thing also gave problems although I wrote what I thought was a > client side fix for this. > > You can have my source in Delphi (such as it is) if you are interested. > > I think there must be flags or dependencies with function managemnt which I > dont understand. > > PS I have not had success editing function in pgaccess either (similar > problems) > > A simple way to view functions is pg_dump -c -s database to just dump schema > and drop statements. Cut and paste function definitions. > > The difficulty of function editing has retarded my putting tasks server-side > which belong there. > > >How can a view a function after it's created ? > >I've created a SQL function that I need to review and possibly change. What > >is the best way to go about this. > > > > > >TIA > > > > Another stuart >
Re: [SQL] Trigger
Craig May wrote: > > Could someone send me a quick example of a trigger. Hope this helps. Chris Ryan <<< Clip below and execute to create example >>> -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); return new; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
Re: [SQL] Trigger
Chris Ryan wrote: > > Craig May wrote: > > > > Could someone send me a quick example of a trigger. > > Hope this helps. > > Chris Ryan > -- snipped code -- I am so sorry but you may have noticed my email client wrapped lines it shouldn't have. I have attached the file this time. Chris Ryan -- -- FILE: trigger_example.sql -- -- DESCRIPTION: -- This file shows the basics of creating a table with a trigger -- -- Chris Ryan <[EMAIL PROTECTED]> 09/06/2000 -- -- GENERAL DISCLAIMER: -- Please feel free to use this in any way you see fit to copy, modify, -- redistribute, etc.. I provide not warranty of the code nor may I be held -- responsible for it's use/misuse should something bad happen including -- intentional or acts of god. -- -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); return new; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
Re: [SQL] Q: spinlock on Alpha? (PG7.0.2)
On Mon, 2 Oct 2000, Tom Lane wrote:
> "Emils Klotins" <[EMAIL PROTECTED]> writes:
> > RedHat Linux 6.2 on Alphaserver DS10 (egcs-1.1.2, glibc-2.1.3,
> > libstdc++-2.9.0).
> > Postgresql-7.0.2 source
>
> > Compiles and installs just fine. However, both the regular initdb and
> > parallel regression testing's initdb stage fails with a core file and
> > message:
>
> > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting.
> > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting.
>
> I was just fooling with PG on a RedHat Alpha box that DEC kindly
> loaned to the project. It looks like the above problem is caused
> by compiler optimization; although src/template/linux_alpha
> optimistically sets CFLAGS=-O2, I had to back off to -O1 to avoid
> that same spinlock complaint, and I couldn't get 7.0.* to pass
> regression tests with anything but -O0. (And even there, there
> were a bunch of failures in the datetime-related tests; it looks
> like our abstime datatype breaks rather badly on this platform.)
To get pgsql running correctly (spinlocks and regression tests) on
Linux/Alpha, one needs the patches off of my web site (as someone already
pointed out in another response to this thread). You can reduce the
optimization levels on the compiler and make some progress, but w/o the
patches, you get nowhere in the end.
Also, some versions of egcs have been known to have problems on
Linux/Alpha, especially when it comes to optimization levels beyond the
default (none). I do know that gcc 2.95.2 (as distributed w/Debian 2.2)
works just fine with -O2. I seem to remember someone mentioning the
compiler with RedHat 6.2 does not work, and they had to upgrade to the
2.95.2 version before the optimization flags were safe.
Of course, there is no release of RH7 for alpha yet (that I know
of), but on the Intel side there are some rumblings that RedHat used a
somewhat broken version of gcc. On top of that, I never quite figured out
the alignment between the egcs and the gcc version numbers (i.e. how does
egcs 1.1.2 relate to gcc 2.95.2?).
I can tell you that with the patches on my web site and the gcc
2.95.2 compiler, everything works great with pgsql. Beyond that, I can not
promise you anything. If anyone wants to test on other compilers, I am
more than glad to help in anyway that I can. And if they report back to me
the results, I will add them to my web page.
> I haven't had time yet to try current sources on that box, but
> I'm optimistic that the new function manager will solve a lot of
> portability problems on Alphas. Still, I don't understand why -O2
> breaks spinlocks --- maybe egcs is misoptimizing around the inline
> assembly code of tas() ?
The last current sources I tested, about a month ago (8/15),
compiled w/o a single problem on my system (Debian 2.2, gcc 2.95.2), w/o
any patches and w/-O2. In other words, the need for special patches for
Linux/Alpha was removed, and now compiles out of the box. Once I get a bit
of time I plan to test more current snapshots, and will of course report
my results.
And yes, the new function manager made about 90% of the 7.0.2
Linux/Alpha patches unneeded. The rest I '#ifdef {linux-alpha}'ed and
submitted a patch (quite short if I remember), which was accepted. Even
patched the regression tests results for geometry to match the Linux/Alpha
output (which actually matched a few other platforms as well). So now even
all regression tests as well. :)
As for the spinlock, it is a compiler problem. At one time it had
non-local labels and therefore could not be inlined (i.e. multiple labels
w/the same name resulteD), but I fixed it by replacing all the labels with
local ones about a year or more ago. Compilers on Linux/Alpha have a
history of misbehaving when it came to the more aggressive optimization.
Only recently has most of the wrinkles been ironed out.
That is my two cents, hope they are of use. TTYL.
---
| "For to me to live is Christ, and to die is gain." |
|--- Philippians 1:21 (KJV) |
---
| Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ |
---
Re: [SQL] Q: spinlock on Alpha? (PG7.0.2) - solved
On Tue, 3 Oct 2000, Emils Klotins wrote: > From: Fabrice Scemama <[EMAIL PROTECTED]> > > Try this: > > http://www.rkirkpat.net/software/ > > > parallel regression testing's initdb stage fails with a core file and > > > message: > > > > > > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. > > > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. > > > > > Thanks, it worked! You are welcome... Some one beat me to directing you to my own site. :) > Now everything works fine and the only regression tests that fail > are > tinterval > abstime > geometry > > I wonder if I might have any problems because of those? The failure with geometry is an off by one error in the nth decimal place, in other words harmless (providing you are not calculating trajectories for an interplanetary space mission :). The other two should not have failed. Have you tried diffing the results against the expected results? Often times timezones being misconfigured can cause strange problems. If there is nothing obvious in the resulting diffs (i.e. nothing that can be traced to a local problem), please email the regression results to me (an attached .tgz is fine), and I will take a look. TTYL. --- | "For to me to live is Christ, and to die is gain."| | --- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | ---
[SQL] [Fwd: [Gborg-bugs] BUG: reference error when using inherited tables (ID: 269) (new)]
I received this bug on a project I administer that Isn't related to my project. I forwarded it here to see if any of you could help this person. [EMAIL PROTECTED] wrote: > > Title: reference error when using inherited tables > Bug Type: Software bug > Severity: Serious > Software Version: Other > Environment: k6III-500/394 > SuSE 7.1 > Postgres 7.1 > > Created By: gorefest > Description: Hi > > I have a problem with inherited refences. > For example : > CREATE TABLE A(LNR integer Primary key blabla); > CREATE TABLE B () INHERITS(A); > CREATE TABLE C(LNR integer primary key blabla, RNR Intger not null, unique(RNR), >FOREIGN KEY(RNR) REFERENCES A.LNr ON DELETE CASCADE); > > will throw an error, if i try to insert an object into B with a counterpart in C. A >with a counterpart in C works. Due to the fact, that the inheritance is an acyclic >graph, the machine should look in B to. But i get a reference error instead. Are >references on inherited tables not implemented yet ? > > greetings gorefest > Status: Submitted > > http://www.greatbridge.org/project/gborg/bugs/bugupdate.php?269 > > ___ > Gborg-bugs mailing list > [EMAIL PROTECTED] > http://www.greatbridge.org/mailman/listinfo/gborg-bugs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] performance issue with distance function
I am trying to execute the following command: SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance(L1.lat, L1.lon, L2.lat, L2.lon) <= 60 LIMIT 100 OFFSET 0 I would rather execute this command: (but i get the error "Attribute 'distance' not found") SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance <= 60 LIMIT 100 OFFSET 0 Having that second distance function in the "WHERE" section of my sql statement is costing me at least 10-20 seconds of execution time. I am looking for a solution that will speed this up. Does anyone have any advice. Thanks in advance. Ryan PS. Here are the respective execution plans: Merge Join (cost=0.00..3463985.82 rows=4342404 width=202) -> Nested Loop (cost=0.00..3461172.63 rows=14735 width=166) -> Nested Loop (cost=0.00..127378.88 rows=147350 width=162) -> Index Scan using restaurantszipcodeidx on restaurants r (cost=0.00..62.50 rows=500 width=138) -> Index Scan using locationszipcodeidx on locations l2 (cost=0.00..251.69 rows=295 width=24) -> Seq Scan on foodtyperestaurantidx ftr (cost=0.00..22.50 rows=10 width=4) -> Index Scan using locationszipcodeidx on locations l1 (cost=0.00..2260.63 rows=29470 width=36) Hash Join (cost=74.08..804150.11 rows=1447468 width=266) -> Nested Loop (cost=0.00..326410.91 rows=2894936 width=120) -> Index Scan using locationszipcodeidx on locations l2 (cost=0.00..251.69 rows=295 width=60) -> Seq Scan on locations l1 (cost=0.00..664.70 rows=29470 width=60) -> Hash (cost=73.96..73.96 rows=50 width=146) -> Merge Join (cost=67.58..73.96 rows=50 width=146) -> Sort (cost=22.67..22.67 rows=10 width=8) -> Seq Scan on foodtyperestaurantidx ftr (cost=0.00..22.50 rows=10 width=8) -> Sort (cost=44.91..44.91 rows=500 width=138) -> Seq Scan on restaurants r (cost=0.00..22.50 rows=500 width=138) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] nearest match
> Ryan wrote: >> I'm doing some work with part diagrams and server-side image maps. I >> want to store single point coordinates (x,y) for reference numbers in >> a table looking like: >> >> reference_number text, >> x int, >> y int >> >> My question is: How can I find the *nearest* match of some clicked on >> coordinates without specifying some arbitrary distance from the stored >> point? > > How about something like this? > > select reference_number, '(50,50)'::point <-> point(x,y) as distance > from my_table order by distance limit 1; > > With a reasonably small table, it should perform fairly well. Hot damn! thats exactly what I needed. I imagine I would only be comparing the distance of 50 points at any given time (about the max number of reference numbers on any given image) so its mighty quick. (184 points takes 1.80 msec to check) I didn't even know about those geometric operators (I find new stuff every day I swear). Are they SQL standard or postgres specific? Ryan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Strings in UDFs
UUGH. Ok...
I am trying to write a pgsql function containing a regular expression within
a substring() function and I just can't fugure it out, and by now I've
wasted way about too much time trying. What am I doing wrong??? I am using
the tool pgManager for debugging & it is creating this DDL in the body:
*
CREATE FUNCTION newid(VARCHAR) RETURNS INTEGER AS'
begin
SELECT INTO maxcnt
CAST (substring( substring( contractcode
from $1 || quote_literal(''#"[0-9]*#"%'')
for quote_literal(''#'')
) from 1 for length(
substring( contractcode
from $1 || quote_literal(''#"[0-9]*#"%'')
for quote_literal(''#''))
)-4) AS int4)
FROM contracts
WHERE contractcode ~* '''' || quote_literal(''^'') || $1 || '''' Order By
contractcnt desc limit 1;
RETURN(maxcnt);
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
*
I've fooled around with it for many hours and keep getting errors relating
to arrays or booleans. g. It seems weird to me that pgManager is trying
to use doubles single-quotes in the quote_literal() function when I entered
(for example) quote_literal('#') in the IDE.
Thanks for any help...
I'm using to Transact-SQL and I'm trying to make the switch to PostgreSQL;
I'm finding the whole idea of escaping the single-quotes very confusing and
so far, frustrating. In addition to helping me with this problem, if anyone
can give me some good advice / general guidelines to using strings in my
functions, it will be greatly appreciated since I anticipate writing a lot
of these soon; it may make a big difference for me. Also, what do you
recommend as the best tool for debugging PL/pgsql functions? Does anyone
find other procedural languages more friendly (like TCL or PYTHON)?
Kind Regards,
-Ryan Riehle
---(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: [SQL] Strings in UDFs
Ok... just got it (finally)... pgManager output the following and it works:
***
CREATE FUNCTION "public"."ftcnum" (VARCHAR) RETURNS INTEGER AS'
DECLARE
maxcnt int4 := 0;
begin
SELECT into maxcnt
CAST (
substring(
substring( contractcode FROM $1 || ''#"[0-9]*#"%'' for
''#'') FROM 1 for
length(
substring( contractcode FROM $1 || ''#"[0-9]*#"%'' for ''#'')
)-4) AS int4) As contractcnt
FROM contracts
WHERE contractcode ~* (''^'' || $1)
Order By contractcnt desc limit 1;
RETURN(maxcnt);
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
***
I removed the use of quote_literal() and had to put parenthesis around the
criteria of the WHERE clause. hrmmm... why is this? does anyone know why
it was giving me an error (something about boolean values) when the WHERE
CLAUSE was: WHERE contractcode ~* (''^'' || $1) ...that was what was really
messing me up before!
-Ryan Riehle
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Ryan Riehle
Sent: Tuesday, March 02, 2004 11:56 AM
To: [EMAIL PROTECTED]
Subject: [SQL] Strings in UDFs
UUGH. Ok...
I am trying to write a pgsql function containing a regular expression within
a substring() function and I just can't fugure it out, and by now I've
wasted way about too much time trying. What am I doing wrong??? I am using
the tool pgManager for debugging & it is creating this DDL in the body:
*
CREATE FUNCTION newid(VARCHAR) RETURNS INTEGER AS'
begin
SELECT INTO maxcnt
CAST (substring( substring( contractcode
from $1 || quote_literal(''#"[0-9]*#"%'')
for quote_literal(''#'')
) from 1 for length(
substring( contractcode
from $1 || quote_literal(''#"[0-9]*#"%'')
for quote_literal(''#''))
)-4) AS int4)
FROM contracts
WHERE contractcode ~* '''' || quote_literal(''^'') || $1 || '''' Order By
contractcnt desc limit 1;
RETURN(maxcnt);
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
*
I've fooled around with it for many hours and keep getting errors relating
to arrays or booleans. g. It seems weird to me that pgManager is trying
to use doubles single-quotes in the quote_literal() function when I entered
(for example) quote_literal('#') in the IDE.
Thanks for any help...
I'm using to Transact-SQL and I'm trying to make the switch to PostgreSQL;
I'm finding the whole idea of escaping the single-quotes very confusing and
so far, frustrating. In addition to helping me with this problem, if anyone
can give me some good advice / general guidelines to using strings in my
functions, it will be greatly appreciated since I anticipate writing a lot
of these soon; it may make a big difference for me. Also, what do you
recommend as the best tool for debugging PL/pgsql functions? Does anyone
find other procedural languages more friendly (like TCL or PYTHON)?
Kind Regards,
-Ryan Riehle
---(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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Design Problem...
Our business has multiple cost/profit centers that I call business units,
these are in a table called buinessunits. We also have a table that holds a
list of services that are offerred by a business. Each business unit has
many services it offers; 1 businees unit => Many Services. We want to be
able to query the cost/revenue/profit generated by each business unit and by
each service. The problem is that it is possible that the service can be
switched to a different business unit, and then possibly back to the
original later on. I've looked at multiple configurations, but have not
found a design that I feel is good so far. Need to somehow track when a
particular service was associated with various businessunits. If you want
more info, I can publish the tables for you. This same type of problem
exists in at least two other areas of this database we are developing.
Please help.
Kind Regards,
-Ryan
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of [EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 5:57 AM
To: [EMAIL PROTECTED]
Subject: Re: [SQL] Order by MM DD in reverse chrono order trouble
Hello,
I'd love to be able to do that, but I cannot just ORDER BY uu.add_date,
because I do not have uu.add_date in the SELECT part of the statement.
The reason I don't have it there is because I need distinct MM DD
values back. Is there a trick that I could use to make this more elegant?
Thanks,
Otis
--- Edmund Bacon <[EMAIL PROTECTED]> wrote:
> Is there some reason you can't do this:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> uu.add_date DESC;
>
> This might be faster, as you only have to sort on one field, and I
> think it should give the desired results
>
> [EMAIL PROTECTED] wrote:
>
> >Hello,
> >
> >I am trying to select distinct dates and order them in the reverse
> >chronological order. Although the column type is TIMESTAMP, in this
> >case I want only , MM, and DD back.
> >
> >I am using the following query, but it's not returning dates back in
> >the reverse chronological order:
> >
> >SELECT DISTINCT
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
> > date_part('day', uu.add_date)
> >
> >FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> >ui.id=uu.user_id WHERE uus.x_id=1
> >
> >ORDER BY
> >date_part('year', uu.add_date), date_part('month', uu.add_date),
> >date_part('day', uu.add_date) DESC;
> >
> >
> >This is what the above query returns:
> >
> > date_part | date_part | date_part
> >---+---+---
> > 2004 | 2 | 6
> > 2004 | 4 |20
> >(2 rows)
> >
> >
> >I am trying to get back something like this:
> >2004 4 20
> >2004 4 19
> >2004 2 6
> >...
> >
> >My query is obviously wrong, but I can't see the mistake. I was
> >wondering if anyone else can see it. Just changing DESC to ASC, did
> >not work.
> >
> >Thank you!
> >Otis
> >
> >
> >---(end of
> broadcast)---
> >TIP 2: you can get off all lists at once with the unregister command
> >(send "unregister YourEmailAddressHere" to
> [EMAIL PROTECTED])
> >
> >
>
> --
> Edmund Bacon <[EMAIL PROTECTED]>
>
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] Invalid Input syntax for type bigint
hello everyone,
I am trying to run the proc below but get an error : invalid input
syntax for type bigint: "2004-10-26" Can anyone suggest what I am
doing wrong here?
Rx
-- Function: public.getdateallocated(date, date)
DROP FUNCTION public.getdateallocated(date, date);
CREATE OR REPLACE FUNCTION public.getdateallocated(date, date)
RETURNS text AS
'Declare
workflow_t ix_workflow_task%ROWTYPE;
BEGIN
SELECT ix_workflow_task."DATE_COMPLETED",
ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE"
INTO workflow_t from ix_workflow_task
INNER JOIN ix_workflow_instance ON
ix_workflow_task."WORKFLOW_INSTANCE_KEY" =
ix_workflow_instance."WORKFLOW_INSTANCE_KEY"
INNER JOIN ix_workflow_instance_to_domain ON
ix_workflow_instance_to_domain."WORKFLOW_INSTANCE_KEY" =
ix_workflow_instance."WORKFLOW_INSTANCE_KEY"
INNER JOIN ix_core_case ON
ix_workflow_instance_to_domain."DOMAIN_KEY" =
ix_core_case."CORECASEKEY"
where to_char(ix_workflow_task."DATE_COMPLETED", \'DD-MM-\') <> \'\'
AND ix_core_case."DELETED" = 0
AND ("CORECASEKEY" in (select * FROM getStatusSwitch($1,$2,
\'Assessment\', \'Prosecution\'))
OR "CORECASEKEY" in (select * from
getStatusSwitch($1,$2, \'Assessment\', \'Investigation\'))
OR "CORECASEKEY" in (select * from
getStatusSwitch($1,$2, \'Assessment\', \'Other\')))
group by ix_workflow_task."DATE_COMPLETED",
ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE"
having (lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'organise surveillance - 9b\' AND ix_workflow_task."TYPE" = \'Human\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID")
LIKE \'start case mix workflow - 9\' AND ix_workflow_task."TYPE" =
\'System\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'finalise case - 13\' AND ix_workflow_task."TYPE" = \'Human\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'complete final priority smart form - 39\' AND
ix_workflow_task."TYPE" = \'Human\'
or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'check for case mix type - 17\' AND ix_workflow_task."TYPE" =
\'System\')
and (ix_workflow_task."DATE_COMPLETED" >= $1 and
ix_workflow_task."DATE_COMPLETED" <= $2);
return workflow_t."WORKFLOW_ACTIVITY_XPDL_ID" ||
to_char(workflow_t."DATE_COMPLETED", \'DD-MM-\');
END;
'
LANGUAGE 'plpgsql' VOLATILE;
select getdateallocated('10/10/04','12/12/04');
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] improvements to query with hierarchical elements
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
[SQL] Working with dates before 4713 BC
Hi all, I am building an application which requires the storage of dates relating to the creation of archaeological items. The problem I am facing is that although most of the dates are working fine, some of the items were created before the beginning of recorded history (4713 BC). Does anyone have any suggestions for making these dates play nicely with items created more recently (January 10th, 1968 for example)? My only other requirement is that I need to be able to execute SQL queries which can find items made before a certain date, after a certain date, or between two dates. Cheers, Ryan
[SQL] Identifying which column matches a full text search
Hi all,
The following example is given in the Postgres 8.3 manual regarding how to
create a single ts_vector column for two existing columns:
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
to_tsvector('english', coalesce(title,'') || coalesce(body,''));
Then we create a GIN index to speed up the search:
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
Now we are ready to perform a fast full text search:
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
Using this approach. Is there any way of retrieving which of the original
two columns the match was found in?
Any help would be much appreciated,
Ryan
Re: [SQL] Identifying which column matches a full text search
Richard Huxton wrote:
>
> Failing that, where I've had many (a dozen) different sources but want
> to search them all I've built a textsearch_blocks table with columns to
> identify the source and have triggers that keep it up to date.
Once you've built the text search blocks table, how do you search it? Do you
perform
twelve separate queries or can you just do one?
Ryan
Ryan Wallace wrote:
>
> UPDATE pgweb SET textsearchable_index_col =
> to_tsvector('english', coalesce(title,'') || coalesce(body,''));
> WHERE textsearchable_index_col @@ to_tsquery('create & table')
> Using this approach. Is there any way of retrieving which of the original
> two columns the match was found in?
Afraid not - you're not indexing two columns, you're indexing one:
textsearchable_index_col.
You can add up to four weights to a tsvector though, typically for
title/body matching. See chapter 12.3 for details.
Failing that, where I've had many (a dozen) different sources but want
to search them all I've built a textsearch_blocks table with columns to
identify the source and have triggers that keep it up to date.
--
Richard Huxton
Archonet Ltd
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.138 / Virus Database: 270.5.6/1579 - Release Date: 7/29/2008
6:43 AM
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Single Quote in tsquery
Hi all, I am trying to perform a full text search for the word 'ksan (which starts with a quote). After much frustration and syntax errors I stumbled upon the following statement which seems to work: select * from items where to_tsvector(name) @@ to_tsquery(E'[\']ksan') I would like to know if this is actually the correct way to search for this word? The use of brackets isn't documented anywhere that I can find so I'm not sure if it is even doing what I want it to do or if the correct result is just a coincidence. Thanks, Ryan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Full Text Index Stats
Greetings, I couldn't find anything about this in the documentation or the mailing list archives, so forgive me if this has already been addressed. I'm trying to determine if the full text indexing has any built-in capability for providing information about word occurrence, i.e. the number of times a given word is used. I read about the ts_stat function, but either I don't understand the syntax well enough or I don't think this is what I'm looking for. I know it can be used for determining the most used words in the index (although this is very slow), but I want to be able to get the number of occurrences on a given word.Is that kind of thing built in somewhere or do I need to write it myself? Thanks!
[SQL] Date Index
Hey all, I'm apparently too lazy to figure this out on my own so maybe one of you can just make it easy on me. J I want to index a timestamp field but I only want the index to include the -mm-dd portion of the date, not the time. I figure this would be where the "expression" portion of the CREATE INDEX syntax would come in, but I'm not sure I understand what the syntax would be for this. Any suggestions? Thanks!
Re: [SQL] Date Index
Incidentally, extract(date from ts) doesn't work on my install of 8.3 (standard Ubuntu Hardy apt install). I get a "timestamp units "date" not recognized" error when I try it. The field I'm trying to create it on is "timestamp without time zone". -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: Friday, October 31, 2008 1:49 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Date Index am Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes: > Hey all, > > > > I?m apparently too lazy to figure this out on my own so maybe one of you can > just make it easy on me. J > > > > I want to index a timestamp field but I only want the index to include the > -mm-dd portion of the date, not the time. I figure this would be where the > ?expression? portion of the CREATE INDEX syntax would come in, but I?m not sure > I understand what the syntax would be for this. > > > > Any suggestions? Sure. You can create an index based on a function, but only if the function is immutable: test=# create table foo (ts timestamptz); CREATE TABLE test=*# create index idx_foo on foo(extract(date from ts)); ERROR: functions in index expression must be marked IMMUTABLE To solve this problem specify the timezone: For the same table as above: test=*# create index idx_foo on foo(extract(date from ts at time zone 'cet')); CREATE INDEX If you have a timestamp whitout timezone it is much easier: test=# create table foo (ts timestamp); CREATE TABLE test=*# create index idx_foo on foo(extract(date from ts)); CREATE INDEX Hope thats help, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Left Join Question
While looking through our data layer code today, I ran across this query: SELECT tasks.*, clients.FirstName, clients.LastName, clients.MiddleInitial, iteminfo.CreatedBy, iteminfo.StationId, iteminfo.CreatedDate, changelog.LastModified, changelog.LastModifiedBy, changelog.LastModifiedAt, ticklers.Due, ticklers.Reminder FROM tasks LEFT JOIN clients ON tasks.ClientId = clients.ClientId LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId LEFT JOIN changelog ON tasks.Id = changelog.ItemId LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId WHERE tasks.Id = '123456'; (I've cleaned it up so it's easier to read.) The basic data structure is that we have a todo list that contains a list of tasks which may or may not be associated with clients, items, log entries, or ticklers (scheduled reminders). The query works as intended: it returns a result-set with all the necessary data to display in the todo list. The performance is not a major concern, although it can be slow for large lists. Since it works, my question is really more about principles: Given that each of the tables in question will contain tens of thousands of rows, is a nested join really the best way to approach this? Thanks! Ryan
Re: [SQL] Left Join Question
Thanks for the feedback, everyone. > > FROM tasks > > LEFT JOIN clients ON tasks.ClientId = clients.ClientId > > LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId > >LEFT JOIN changelog ON tasks.Id = changelog.ItemId > > LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId > > WHERE tasks.Id = '123456'; > > > > (I've cleaned it up so it's easier to read.) > > Thanks Ryan - that always makes it easier. It actually looked so much less crazy after the cleaning that I nearly decided not to ask about it, but my I was really interested in learning if there was a dramatically better approach. Looks like there probably isn't, which is fine. > For the case when you have large numbers of results from iteminfo etc. > it might well be quicker to do separate queries. That's simple enough > since you're joining straight to tasks.id, but is obviously more fiddly > and when there aren't many rows returned would probably be slower (you'd > have 5 lots of query parsing/execute overhead). That might be a > trade-off that makes sense to you though. Even though the tables are fairly big, the result-set should be small (a few dozen maybe), so multiple queries probably wouldn't gain much. Thanks again! Ryan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Use of index in 7.0 vs 6.5
Tom (Or anyone else who is good with PostgreSQL statistics), I am in the process of transitioning from postgreSQL 6.5.3 to postgreSQL 7.0. I ran into an issue where a sequential scan is being choosen on postgreSQL 7.0 where an index scan was choosen on postgreSQL 6.5.3. Note: All tables have been freshly vacuum'd and analyzed. procman=# select version(); version --- PostgreSQL 7.0.0 on hppa2.0w-hp-hpux11.00, compiled by gcc 2.95.2 (1 row) procman=# explain select count(catagory) from medusa where host_id = 404 and catagory like 'A%'; NOTICE: QUERY PLAN: Aggregate (cost=189546.19..189546.19 rows=1 width=12) -> Seq Scan on medusa (cost=0.00..189529.43 rows=6704 width=12) EXPLAIN Note: The above query produces an index scan on postgreSQL 6.5.3. procman=# set enable_seqscan = off; SET VARIABLE procman=# explain select count(catagory) from medusa where host_id = 404 and catagory like 'A%'; NOTICE: QUERY PLAN: Aggregate (cost=207347.36..207347.36 rows=1 width=12) -> Index Scan using medusa_host_id_key on medusa (cost=0.00..207330.60 rows=6704 width=12) EXPLAIN Here are the statistics: procman=# select attname,attdisbursion,s.* procman-# from pg_statistic s, pg_attribute a, pg_class c procman-# where starelid = c.oid and attrelid = c.oid and staattnum = attnum procman-# and relname = 'medusa'; attname | attdisbursion | starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval |stahival ---+---+--+---+---+-+---+-+ --+- host_id |0.00621312 | 30874288 | 1 |97 | 0 | 0.0279425 | 446 | 0 | 11011 (1 row) Here is my analysis of the stastics (based on the examples in the archive). The most common value host_id in the table is 446 with row fraction of ~ 2.8%. The estimated number of rows in the index is 6704. This table has 4,630,229 entries in the table. Hopefully this analysis is correct, if not .. please correct me :) I do not understand why the planner would choose a seqscan over the index scan because 6704/4,630,229 is ~ 0.15%. Thanks for your time, Ryan - Ryan
Re: [SQL] Use of index in 7.0 vs 6.5
Tom Lane wrote: > Ryan Bradetich <[EMAIL PROTECTED]> writes: > > I am in the process of transitioning from postgreSQL 6.5.3 to > > postgreSQL 7.0. I ran into an issue where a sequential scan > > is being choosen on postgreSQL 7.0 where an index scan was > > choosen on postgreSQL 6.5.3. > > Since you're complaining, I assume the seqscan is slower ;-). > But you didn't say how much slower --- what are the actual timings? Opps... Had them written down, just forgot to include them in the email :) with enable_seqscan = on: real 18.05 sys0.01 user 0.02 with enable_seqscan = off: real 0.08 sys 0.01 user 0.02 I stopped and restarted the postmaster daemon between these timing to flush the cache. > Basically what's going on here is that we need to tune the fudge-factor > constants in the cost model so that they have something to do with > reality on as wide a variety of systems as possible. You did an > excellent job of showing the estimates the planner computed --- but > what we really need here is to see how those relate to reality. > > > I do not understand why the planner would choose a seqscan over the > > index scan because 6704/4,630,229 is ~ 0.15%. > > I'm a bit surprised too. What is the average tuple width on this table? > (Actually, probably a better question is how many pages and tuples > are in the relation according to its pg_class entry. Try "select * from > pgclass where relname = 'medusa'".) > > regards, tom lane procman=# select * from pg_class where relname = 'medusa'; relname | reltype | relowner | relam | relpages | reltuples | rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhas pkey | relhasrules | relacl -+-+--+---+--+---+--+-+-+-+--+---+-+--+--+-+--- -+-+ medusa | 0 |36000 | 0 | 120076 | 4630229 |0 | t | f | r |6 | 0 | 0 |0 |0 | 0 | f | f | (1 row) procman=# \d medusa Table "medusa" Attribute | Type| Modifier ---+---+-- host_id | integer | timestamp | timestamp | current | integer | catagory | text | cat_desc | text | anomaly | text | This table has two fairly large text fields, the cat_desc and the anomaly. The catagory field is very short and in the format: [ABC][0-9][0-9]. Thanks for the help, - Ryan
Re: [SQL] Use of index in 7.0 vs 6.5
Tom Lane wrote: > "Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > >> One way to put a thumb on the scales is to reduce the value of the SET > >> variable random_page_cost. The default value is 4.0, which seems to > >> correspond more or less to reality, but reducing it to 3 or so would > >> shift the planner pretty nicely in the direction of indexscans. This worked great! Is their a place I can change the default to 3? I do not want to change all the scripts to include this :) > > Or how about changing current fudge factor ? > > For example,from 0.5 to 0.2 which is the fudge factor of attdisbursion > > calculation. > > Yes, that's another way --- and probably more defensible than changing > random_page_cost, now that I think about it. Unfortunately it's a > hardwired constant and so not as easily experimented with :-(. > > regards, tom lane Can you give me more information about this? I do not have a problem re-compiling the database and performing more testing if you would like. Tom, To answer your question in a previous post: Since you find that in reality the indexscan method is very quick, I'm guessing that there are actually fairly few tuples matching host_id = 404. Could you run a quick "select count(*)" to check? procman=# select count(*) from medusa where host_id = 404; count --- 680 (1 row) procman=# select count(catagory) from medusa where host_id = 404 and catagory like 'A%'; count --- 4 (1 row) Thanks again everyone for all the help! Now that I am finished with school for the semester, I should have time to make contributions again ... :) Ryan
Re: [SQL] Use of index in 7.0 vs 6.5
Tom Lane wrote: > Ryan Bradetich <[EMAIL PROTECTED]> writes: > > This worked great! Is their a place I can change the default to 3? > > I do not want to change all the scripts to include this :) > > See src/include/optimizer/cost.h. However, I am currently thinking of > taking Hiroshi's advice instead. Lowering RANDOM_PAGE_COST seems like > a bad idea --- if anything, we might want to raise it ;-) > > >>>> Or how about changing current fudge factor ? > >>>> For example,from 0.5 to 0.2 which is the fudge factor of attdisbursion > >>>> calculation. > >> > >> Yes, that's another way --- and probably more defensible than changing > >> random_page_cost, now that I think about it. Unfortunately it's a > >> hardwired constant and so not as easily experimented with :-(. > > > Can you give me more information about this? I do not have a problem > > re-compiling the database and performing more testing if you would like. > > The fudge factor in question is currently 0.5, and is used in two places > in src/backend/utils/adt/selfuncs.c (looks like lines 193 and 212 in 7.0 > sources). I was thinking of dropping it to 0.25 or 0.1. > > regards, tom lane Tom and Hiroshi, I modified the backend to 0.1 and this has been working great! Thanks again for the suggestion, and I'll let you know if we run into a problem. Thanks again! Ryan
[SQL] Sequences do not obey transactions...
Either I am missing something or I found a bug in PostgreSQL. Hopefully it is the former. :) Simply, I am trying to use a sequence to generate unique id numbers for a table. Now, a number of the fields in this table have 'check constraints'. What happens, is if I attempt to insert a row into the table that fails to meet the constraints and is rejected, the sequence is still incremented. Therefore, for each failed insert, a hole results in my id number sequence. While this is not fatal, it is very annoying. I even tried wrapping a BEGIN / END around a failing insert and the sequence still incremented. It appears that whenever the 'nextval' function is called, no matter where, in a failing insert, inside an aborted transaction, etc..., the changes it makes to the sequence are permanent. So is this supposed to be this way, or did I stumble across a bug? If the former, would some one please explain why this is this way (and possibly add it to the documenation). Thanks. PS. The mailing list search engines on the pgsql web site are broken. They either find nothing, no matter what search terms one enters, or complain about not being able to find the needed tables (relations). --- | "For to me to live is Christ, and to die is gain."| |--- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | ---
[SQL] Re: [GENERAL] Sequences do not obey transactions...
On Wed, 21 Jun 2000, Haroldo Stenger wrote: > This issue has been asked & answered MANY times, once a week perhaps. I'll copy > here what a folk answered once Hmm.. Then if the email archive searchs on the web site had been working I would not have had to was the list's time. As for the comments by people that pgsql's sequence behavior is well documented, please tell me where! I have looked through the HTML does and the FAQ that comes with Pgsql 7.0.2 and found no mention of it. > "You can't. Sequences are not designed for continuity, they are designed for > uniqueness. Now that I think about it (again w/other people's explainations taken into account) pgsql's behavior now makes sense to me. I was just looking for a continuous sequence of unique numbers and thought a sequence might be handy. Guess not. :( Thanks for everyone's explaination. > Haroldo Stenger wrote: > > And I add one of my own: It is not really necessary to have continuity in nearly > > all apps. Your question is valid anyhow, but ask yourself: How does Oracle > > resolve this? How would I program it myself by hand? And there you'll understand > > the issue deeply. > > How funny it is to quote myself :9 I'd like to add this: When one has to number > paper forms (invoices for instance), one must be careful about holes in > sequences. What I do, is to have two sequences. One for the user, and one for > the system. When the user has confirmed all her data, I'll COPY to another table > the data, which NOW doesn't have a chance to cancel. Well, not so sure, but 99% > of aborts, are user aborts. So this may help. That is similar to what I was attempting to do. I am making an inventory database where each piece of computer equipment has a unique number assigned to it. A sequence would save me having to figure out what number was next. But if it had holes in the sequence, then I would end up wasting the sequential labels I had already printed. :( Given my situtation, I think I will just use the label sheet to tell me which number is next and enter it in from there. Low-tech, but should work. :) > Note to Bruce (or current FAQ mantainer): Please, add both the answer to the > very question, and this addition of my own to the FAQ. I would have loved to > find it somewhere, when I didn't know what to do. Yes, please do. I always search the documentation and email list archives (if available) before asking a question. Guess in this case I hit a question that fell through the cracks before making it into the FAQ. Once again, thanks for everyone's help. --- | "For to me to live is Christ, and to die is gain."| | --- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | ---
[SQL] Problem with pg_dumpall
Hello all, I am having a new problem with pg_dumpall that I have not seen before. I've been browsing the documentation and could not find anything related to this problem. Any ideas or pointers would greatly be appreciated. boi260 sanity $ /opt/pgsql/bin/pg_dumpall -v -o | /usr/contrib/bin/gzip -c > /opt/pgsql/backup/db.pgdump.gz [ Extra verbage snipped ] -- dumping out the contents of Table 'medusa' FATAL 1: Memory exhausted in AllocSetAlloc() PQendcopy: resetting connection SQL query to dump the contents of Table 'medusa' did not execute correctly. After we read all the table contents from the backend, PQendcopy() failed. Explanation from backend: 'FATAL 1: Memory exhausted in AllocSetAlloc() '. The query was: 'COPY "medusa" WITH OIDS TO stdout; '. pg_dump failed on procman, exiting procman=# select version(); version --- PostgreSQL 7.0.0 on hppa2.0w-hp-hpux11.00, compiled by gcc 2.95.2 (1 row) Thanks, - Ryan
Re: [SQL] Problem with pg_dumpall
Tom Lane wrote: > Ryan Bradetich <[EMAIL PROTECTED]> writes: > > -- dumping out the contents of Table 'medusa' > > FATAL 1: Memory exhausted in AllocSetAlloc() > > PQendcopy: resetting connection > > SQL query to dump the contents of Table 'medusa' did not execute > > correctly. After we read all the table contents from the backend, > > PQendcopy() failed. Explanation from backend: 'FATAL 1: Memory > > exhausted in AllocSetAlloc() > > '. > > The query was: 'COPY "medusa" WITH OIDS TO stdout; > > Hmm. What is the full definition of that table? (pg_dump -s -t medusa > would do.) > > regards, tom lane Tom, boi260 /data08 $ pg_dump -s -t medusa procman \connect - postgres CREATE TABLE "medusa" ( "host_id" int4, "timestamp" timestamp, "current" int4, "catagory" text, "cat_desc" text, "anomaly" text ); CREATE INDEX "medusa_catagory_key" on "medusa" using btree ( "catagory" "text_ops" ); CREATE INDEX "medusa_host_id_key" on "medusa" using btree ( "host_id" "int4_ops" ); CREATE INDEX "medusa_current_key" on "medusa" using btree ( "current" "int4_ops" ); Ryan
Re: [SQL] Problem with pg_dumpall
Tom Lane wrote: > Ryan Bradetich <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Ryan Bradetich <[EMAIL PROTECTED]> writes: > >>>> -- dumping out the contents of Table 'medusa' > >>>> FATAL 1: Memory exhausted in AllocSetAlloc() > >>>> PQendcopy: resetting connection > >>>> SQL query to dump the contents of Table 'medusa' did not execute > >>>> correctly. After we read all the table contents from the backend, > >>>> PQendcopy() failed. Explanation from backend: 'FATAL 1: Memory > >>>> exhausted in AllocSetAlloc() > >>>> '. > >>>> The query was: 'COPY "medusa" WITH OIDS TO stdout; > > Now that I look at it, it appears that COPY WITH OIDS leaks the memory > used for the string representation of the OIDs. That'd probably cost > you 32 bytes or so of backend memory per row --- which you'd get back > at the end of the COPY, but small comfort if you ran out before that. > > Is the table large enough to make that a plausible explanation? > > regards, tom lane Tom, This table is very large so that could be the problem. Here are the startup parameters I am using (in case it matters): -B 1024 -S -o -F -o -o /home/postgres/nohup.out -i -p 5432 -D/data08 nohup su - postgres -c "/opt/pgsql/bin/postmaster -B 1024 -S -o \"-F\" -o \"-o /home/postgres/nohup.out\" -i -p 5432 -D/data08" procman=# select count(*) from medusa; count - 6986499 (1 row) FYI: That was the problem. Good job at spotting that Tom. I just successfully completed a backup without using the -o option to pg_dumpall. Thanks again for the help! - Ryan -- Ryan Bradetich AIT Operations Unix Platform Team
