Re: [GENERAL] Is PostGreSql's Data storage mechanism "inferior"?
--- On Wed, 1/30/08, Swaminathan Saikumar <[EMAIL PROTECTED]> wrote: > The cons of PostgreSql Hosting > * Performance considerations: Inserts and Updates into the > PostgreSql database is much slower compared to MySql. PostgreSql > hosting thus might slow down the display of the web page online. " ... might slow down ..." I guess they didn't check to know one way or ther other whether It does slow down a web page. The real answer depends upon your they kind of uses you have. > * BSD license issues: Since PostgreSql comes under the > Berkeley license scheme, this is again considered to be too open. What does "too open" mean? Is too open a bad thing? > * Availability of inferior Data Storage mechanism: > PostgreSql uses Postgres storage system, which is not considered to be > transaction sae during PostgreSql hosting. It seems they misspelled "safe". But in either case they also seemed to have confused MySQL with PostgreSQL on this point. Being transactionally "safe" is one of PostgreSQL's strongest points. > * Its not far-flung: While MySql hosting and MSSql hosting have deeply > penetrated into the market, PostgreSql hosting still remains to be passive in > the database hosting market. My gut says that this is probably true. But there are very good hosting sites that provide PostgreSQL. > * Non-availability of required assistance for PostgreSQL hosting: Assistance > is being provided via mailing lists. However there is no guarantee that the > issue faced during PostgreSQL hosting would be resolved. If you have a highly critical application that requires guaranteed assistance you are more than able to purchase it from some top notch consultant companies that support PostgreSQL. Just check out the PostgreSQL home page. So I don't this this point is correct either. If you don't get too much feed back on this subject, just remember that topics like this come up frequently to the point of list member exhaustion. You can find such discussions if you search the list archive. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] referencing to "computed columns" in where clause
--- On Tue, 1/29/08, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: >> other than defining a function such that I can write: SELECT ( a-b ) as pippo FROM Yourtable WHERE ( a-b ) = 7 UNION ALL SELECT ( a*b ) as pippo FROM Yourtable WHERE ( a*b ) < 12 AND a > 3 UNION ALL SELECT ( a+b ) as pippo FROM Yourtable WHERE ( a+b ) < 12 AND a < 3; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] altering foreign keys
--- On Tue, 1/22/08, Adam Rich <[EMAIL PROTECTED]> wrote: > Is there a way to alter the > existing keys? (it didn't jump out at me in the manual) ALTER TABLE your_table DROP CONSTRAINT your_column_fkey_constraint, ADD CONSTRAINT your_column_fkey_constraint FOREIGN KEY your_column REFERENCES parent_table( primary_column ) ON UPDATE CASCADE; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] many to one of many modeling question
--- On Fri, 1/11/08, David Fetter <[EMAIL PROTECTED]> wrote: > > One method > > that has been proposed is to have a third table which > stores to what object > > type a comment belongs, but I don't like this > because the foreign key > > relationships then wouldn't be maintained by the > database. The only way > > that I'm able to think of at the moment is > multiple columns. Actually, I think if you notice the example I posted previously, I made both the Unique ID column and object type column a composite primary key. The other tables referenced this composite primary key as foreign keys using referential integrity ON UPDATE CASCADE. This means that they database ~would~ maintain/enforce the object-type portion of the foreign key automatically for you. Regards, Richard Broersma Jr. ---(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: [GENERAL] Announcing PostgreSQL RPM Buildfarm
--- On Mon, 1/7/08, Devrim GÜNDÜZ <[EMAIL PROTECTED]> wrote: > wrote: > > what is a botherboard? > > Brotherboard -- motherboard is a bit old. Maybe a Freudian slip? ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] many to one of many modeling question
--- On Mon, 1/7/08, Erik Jones <[EMAIL PROTECTED]> wrote: > From: Erik Jones <[EMAIL PROTECTED]> > Subject: Re: [GENERAL] many to one of many modeling question > To: "Richard Broersma Jr" <[EMAIL PROTECTED]> > Cc: "Postgres General List" , "Kevin Hunter" > <[EMAIL PROTECTED]>, "Caktus Team" <[EMAIL PROTECTED]> > Date: Monday, January 7, 2008, 3:06 PM > On Jan 7, 2008, at 2:22 PM, Richard Broersma Jr wrote: > > > CREATE TABLE comments ( > > id SERIAL, > > obj_id INTEGER ... > > FOREIGN KEY (obj_id) > > REFERENCES ONE OF o(id); > > Where are you guys getting this REFERENCES ONE OF stuff? Oops, my mistake. The OP was using this fake syntax "REFERENCES ONE OF" simply to illustrate the functionality that was desired. I forgot to delete this bad syntax from my example. Thanks for the correction! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] many to one of many modeling question
--- On Mon, 1/7/08, Kevin Hunter <[EMAIL PROTECTED]> wrote: > :-( Yeah this is one method. I was hoping for something > cleaner though. > Something along the lines of > > CREATE TABLE o_1 ( id SERIAL ... ); > CREATE TABLE o_2 ( id SERIAL ... ); > CREATE TABLE o_3 ( id SERIAL ... ); > CREATE TABLE comments ( > id SERIAL, > obj_id INTEGER ... > FOREIGN KEY (obj_id) REFERENCES ONE OF o_1(id), > o_2(id), o_3(id) > ); you can get this to work if you create a table hiarachy instead where o_1, o_2, and o_3 area derived from a parent table o. CREATE TABLE o ( id SERIAL UNIQUE NOT NULL, obj_type VARCHAR CHECK( obj_type IN (1,2,3)) NOT NULL, PRIMARY KEY (id, obj_type) ... ); CREATE TABLE o_1 ( id INTEGER UNIQUE NOT NULL, obj_type VARCHAR CHECK( obj_type = 1 ), PRIMARY KEY (id, obj_type), FOREIGN KEY (id, obj_type) REFERENCES o(id,obj_type) ON DELETE CASCADE ON UPDATE CASCADE, ... ); CREATE TABLE o_2 ( id INTEGER UNIQUE NOT NULL, obj_type VARCHAR CHECK( obj_type = 2 ), PRIMARY KEY ( id, obj_type), FOREIGN KEY (id, obj_type) REFERENCES o(id,obj_type) ON DELETE CASCADE ON UPDATE CASCADE, ... ); CREATE TABLE o_3 ( id INTEGER UNIQUE NOT NULL, obj_type VARCHAR CHECK( obj_type = 3 ), PRIMARY KEY ( id, obj_type), FOREIGN KEY (id, obj_type) REFERENCES o(id,obj_type) ON DELETE CASCADE ON UPDATE CASCADE, ... ); CREATE TABLE comments ( id SERIAL, obj_id INTEGER ... FOREIGN KEY (obj_id) REFERENCES ONE OF o(id), ... ); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] visibility rules for AFTER UPDATE Constraint Triggers Function
--- On Wed, 1/2/08, Tom Lane <[EMAIL PROTECTED]> wrote: > [ pokes at it... ] The reason is that you defined both the trigger and > the testing function as STABLE, which means that they see a snapshot of > the database as of the start of the calling SQL command. In the first > case that's the UPDATE, in the second it's the COMMIT. Thanks for the help Tom. This information is good to know. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] visibility rules for AFTER UPDATE Constraint Triggers Function
--- On Tue, 1/1/08, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> Please provide a self-contained example.
Basically the example demonstrates the difference a single tuple UPDATE when
preformed the following two ways:
UPDATE table ...; -- Sees OLD.
BEGIN; UPDATE table ...; COMMIT; --Sees NEW.
I was my understanding that all single DML statement are wrapped in their own
transaction so I thought that these two statements should preform the same.
Regards,
Richard Broersma Jr.--
-- PostgreSQL database dump
--
-- Started on 2008-01-02 05:35:55
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 7 (class 2615 OID 605027)
-- Name: instrumentation; Type: SCHEMA; Schema: -; Owner: teaminst
--
CREATE SCHEMA instrumentation;
ALTER SCHEMA instrumentation OWNER TO teaminst;
--
-- TOC entry 1784 (class 0 OID 0)
-- Dependencies: 6
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
--
-- TOC entry 325 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--
CREATE PROCEDURAL LANGUAGE plpgsql;
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
SET search_path = instrumentation, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 1502 (class 1259 OID 605179)
-- Dependencies: 1769 1770 1771 1772 1773 1774 1775 1776 7
-- Name: looptimelines; Type: TABLE; Schema: instrumentation; Owner: teaminst;
Tablespace:
--
CREATE TABLE looptimelines (
startdate date DEFAULT ('now'::text)::date NOT NULL,
enddate date DEFAULT '-12-31'::date NOT NULL,
startproject_code character varying(15) NOT NULL,
endproject_code character varying(15),
area integer NOT NULL,
process character(1) NOT NULL,
loop_nbr integer NOT NULL,
suffix character(1) DEFAULT ''::bpchar NOT NULL,
service_desc text DEFAULT '--HOLD--'::text NOT NULL,
CONSTRAINT looptimelines_area_check CHECK (((area >= 0) AND (area <= 99))),
CONSTRAINT looptimelines_loop_nbr_check CHECK (((loop_nbr >= 1) AND
(loop_nbr <= ))),
CONSTRAINT looptimelines_process_check CHECK (((process >= 'A'::bpchar) AND
(process <= 'Z'::bpchar))),
CONSTRAINT looptimelines_suffix_check CHECK suffix >= 'A'::bpchar) AND
(suffix <= 'Z'::bpchar)) OR (suffix = ''::bpchar)))
);
ALTER TABLE instrumentation.looptimelines OWNER TO teaminst;
SET search_path = public, pg_catalog;
--
-- TOC entry 276 (class 1247 OID 16405)
-- Dependencies: 6 1497
-- Name: breakpoint; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE breakpoint AS (
func oid,
linenumber integer,
targetname text
);
ALTER TYPE public.breakpoint OWNER TO postgres;
--
-- TOC entry 278 (class 1247 OID 16408)
-- Dependencies: 6 1498
-- Name: frame; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE frame AS (
level integer,
targetname text,
func oid,
linenumber integer,
args text
);
ALTER TYPE public.frame OWNER TO postgres;
--
-- TOC entry 320 (class 1247 OID 16417)
-- Dependencies: 6 1501
-- Name: proxyinfo; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE proxyinfo AS (
serverversionstr text,
serverversionnum integer,
proxyapiver integer,
serverprocessid integer
);
ALTER TYPE public.proxyinfo OWNER TO postgres;
--
-- TOC entry 316 (class 1247 OID 16411)
-- Dependencies: 6 1499
-- Name: targetinfo; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE targetinfo AS (
target oid,
schema oid,
nargs integer,
argtypes oidvector,
targetname name,
argmodes "char"[],
argnames text[],
targetlang oid,
fqname text,
returnsset boolean,
returntype oid
);
ALTER TYPE public.targetinfo OWNER TO postgres;
--
-- TOC entry 318 (class 1247 OID 16414)
-- Dependencies: 6 1500
-- Name: var; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE var AS (
name text,
varclass character(1),
linenumber integer,
isunique boolean,
isconst boolean,
isnotnull boolean,
dtype oid,
value text
);
ALTER TYPE public.var OWNER TO postgres;
SET search_path = instrumentation, pg_catalog;
--
-- TOC entry 41 (class 1255 OID 605215)
-- Dependencies: 7 325 322 322
-- Name: sample_for_new_or_old(looptimelines, looptimelines); Type: FUNCTION;
Schema: instrumentation; Owner: postgres
--
CREATE FUNCTION sample_for_new_or_old(mynew looptimelines, myold looptimelines)
RETURNS character varying
AS $$
BEGIN
IF 'NEW'::VARCHAR = ( SELECT 'NEW'
[GENERAL] visibility rules for AFTER UPDATE Constraint Triggers Function
I have a PLpgSQL AFTER UPDATE constraint trigger that calls a trigger function with an embedded select statement. The select statement queries the affected table and returns a mult-result set that includes the record equivalent to NEW. However, It seems the select doesn't yet see the non-committed records. I.E. the record that should be equal to NEW is in fact equal to OLD. Is this correct? Is there a way to adjust the visibility so that yet-to-be committed tuples are seen by the trigger function? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] timestamptz & 'infinity' & date
I am getting a little confused in casting 'infinity'. It seems that 'infinity' still can't be casted to a date. :-( But I tried the following casts, but I am not sure the results are consistent. instrumentation=> SELECT 'infinity'::date; ERROR: invalid input syntax for type date: "infinity" instrumentation=> SELECT 'infinity'::timestamptz; timestamptz - infinity (1 row) instrumentation=> SELECT 'infinity'::timestamptz::date; date -- <-- this blank I determine was a NULL. (1 row) Is this correct? Shouldn't it also return an error? Regards, Richard Broersma Jr. ---(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: [GENERAL] weird date/times in mailing list
--- On Thu, 12/27/07, Thomas Hart <[EMAIL PROTECTED]> wrote: > Am I the only one seeing very strange send times for the > last dozen messages or so, or is something else going on with my end > (maybe my mail server is catching up...) Even stranger is the fact that most of these posts are prefixed with [TLM]. I've noticed that one of the emails that I posted a couple of days ago was reciently reposted with the prefix [TLM]. I wonder if someone is bouncing emails from this list. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[TLM] Re: [GENERAL] how to alter an enum type
--- On Mon, 12/24/07, Henrique Pantarotto <[EMAIL PROTECTED]> wrote: > I tried searching the documentation and mailing list, and I > couldn't > figure this one out. ALTER TABLE questions ALTER COLUMN answers TYPE possible_answers; Actually your type is fine. you only need to alter the column on your table to use your new type. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
[TLM] Re: [GENERAL] pg_ctl start fails on Windows
--- On Mon, 12/24/07, Abraham, Danny <[EMAIL PROTECTED]> wrote: > I can manually create the file "postmaster.pid", > so the problem > Does not look like a permission problem. I had a similar problem once. For some reason, after I installed a windows service pack update, my Postgresql directory was altered to be READ-ONLY. Once this happened, postgresql wan't able to start. After spending a quited a bit of time to dicover the cause of the problem, I simply had to reset the directoy to be write-able. I hope this helps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] copy database by copying datafiles ?
--- On Mon, 12/24/07, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > P.S. datafiles are 85GB in size, I couldn't really dump > and restore... Don't for get the steps of compressing and uncompressing between dump and restore.;) If the file is still too big, you can always use tar to spit the file up to make the transition a bit more manageable. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] how to alter an enum type
--- On Mon, 12/24/07, Henrique Pantarotto <[EMAIL PROTECTED]> wrote: > I actually want to change the enum values after I have > created and > associated it to a table colum. It looks like you will have to drop the type and re-create it. You might have to try a couple of tests: 1) BEGIN TRANSACTION; DROP the type create the type commit; --does your table have still reference this type? 2) BEGIN TRANSACTION; CREATE a new type with a different name alter the table column to refer to the new type drop the old type Commit; I wonder if it is possible to create an enum type using a select statement. This way the contents of the type can be seen in a base table. and it would be easy to recreate the type when new elements are added or removed. I'll have to play around with this in the future to see what is possible. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_ctl start fails on Windows
--- On Mon, 12/24/07, Abraham, Danny <[EMAIL PROTECTED]> wrote: > I can manually create the file "postmaster.pid", > so the problem > Does not look like a permission problem. I had a similar problem once. For some reason, after I installed a windows service pack update, my Postgresql directory was altered to be READ-ONLY. Once this happened, postgresql wan't able to start. After spending a quited a bit of time to dicover the cause of the problem, I simply had to reset the directoy to be write-able. I hope this helps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] how to alter an enum type
--- On Mon, 12/24/07, Henrique Pantarotto <[EMAIL PROTECTED]> wrote: > I tried searching the documentation and mailing list, and I > couldn't > figure this one out. ALTER TABLE questions ALTER COLUMN answers TYPE possible_answers; Actually your type is fine. you only need to alter the column on your table to use your new type. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] possible psql \d bug in 8.3 beta2
--- On Sun, 12/23/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > -+-+---+-- > > project | managers| table | teaminst > > It's hidden by the "managers" table in the > earlier "history" schema. > \d without any particular schema specification will > only show tables > that could be accessed by an unqualified table name. I see. Thanks for the clarification. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] possible psql \d bug in 8.3 beta2
Is seems that \d is missing the following entry when I set search_path to display two schemas: List of relations Schema |Name | Type | Owner -+-+---+-- project | managers| table | teaminst instrumentation=> select version(); version PostgreSQL 8.3beta2, compiled by Visual C++ build 1400 (1 row) instrumentation=> set search_path to project; SET instrumentation=> \d List of relations Schema |Name | Type | Owner -+-+---+-- project | managers| table | teaminst project | project_tls | table | teaminst project | projects| table | teaminst (3 rows) instrumentation=> set search_path to history; SET instrumentation=> \d List of relations Schema | Name | Type | Owner -+--+---+-- history | managers | table | teaminst (1 row) instrumentation=> set search_path to history, project; SET instrumentation=> \d List of relations Schema |Name | Type | Owner -+-+---+-- history | managers| table | teaminst <--where is project.managers project | project_tls | table | teaminst project | projects| table | teaminst (3 rows) instrumentation=> Regards, Richard Broersma Jr. ---(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: [GENERAL] Constraint Trigger's referenced_table
--- On Sun, 12/23/07, Tom Lane <[EMAIL PROTECTED]> wrote: > (and, depending on which trigger you are talking about, perhaps > make it hold by changing the other table). Okay, I take it that changing the other table would only apply to tables that were designed with foreign key constraints that have ON UPDATE or ON DELETE actions set. I think I understand now. Thanks for the clarification. Regards, Richard Broersma JR. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Constraint Trigger's referenced_table
--- On Sat, 12/22/07, Tom Lane <[EMAIL PROTECTED]> wrote: > No, the purpose is to support foreign-key triggers. FK constraints are > implemented via cooperating triggers on the two tables, and > each trigger has to be able to look at the other table. When you say "each trigger has to be able to look" do you mean that each trigger needs to be notified when other table has records that are inserted/updated/deleted? >From what I gathered from the reading of ri_triggers.c, FWIU it seems that >this kind of cross table checking is designed to seek out any FKs or PKs in >the related table and then execute a validating query that compares the PK to >FK. Can this kind of cross checking still be implemented for temporal relations where foreign keys are not actually implemented in the "referencing" table. For example I want to enforce the primary-relation's time range to always engulf the time range of the foreign-relation.: PK timeline: |---p1---|--p2--|-p3-|p4|---p5---| FK timeline: |-f1-|f2|--f3--|--f4--| So if I INSERT/UPDATE/DELETE records to either table, would implementing the "FROM referenced_table" predicated of the CREATE Constraint Trigger enable this kind of notification to perform cross checking even though there are strictly no pk/fk relations between these table? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Constraint Trigger's referenced_table
--- On Sat, 12/22/07, Tom Lane <[EMAIL PROTECTED]> wrote: > There's always the source code: > src/backend/utils/adt/ri_triggers.c Thanks. I will do my best and give it a try :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Constraint Trigger's referenced_table
--- On Sat, 12/22/07, Tom Lane <[EMAIL PROTECTED]> wrote: > No, the purpose is to support foreign-key triggers. FK > constraints are > implemented via cooperating triggers on the two tables, and > each trigger > has to be able to look at the other table. Sorry Tom, I guess I am still a bit confused about how all of this works and if there are any useful ways I can use this feature to enforce the slightly more complicated temporal foreign-key RI. Does any documentation exist that I could spend some time reading that explains detail the cooperation between triggers on the two tables? Regards, Richard Broersma Jr. ---(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: [GENERAL] Constraint Trigger's referenced_table
--- On Sat, 12/22/07, Tom Lane <[EMAIL PROTECTED]> wrote: > It doesn't. That table OID is just stored for the > trigger function > to use if it wants to. I see. I was thinking that the FROM clause of the Constraint Trigger would allow tuple modifications from either the ON table or FROM table to raise the raise the trigger. So is the purpose of knowing the reference_table OID, is that it helps to generalize the logic of the CONSTRAINT TRIGGERS? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Requirements for Constraint Trigger's Function
--- On Sat, 12/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > Should it return some sort of value? Should it raise > an exception? > > The latter. Thanks Michael! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Constraint Trigger's referenced_table
How does a Constraint Trigger react to a referenced table when the constraint is created implementing the FROM clause? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Requirements for Constraint Trigger's Function
What is the proper way for the function of a constraint trigger to signal where or not referential integrity was compromised? Should it return some sort of value? Should it raise an exception? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Exactly what is DEFERRABLE
--- On Fri, 12/21/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > Does this imply that custom CONSTRAINT TRIGGERs are not > DEFERRABLE? Or are they? Sorry dumb question. They must be deferrable since their create statement allows for differable. Regard,s Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Exactly what is DEFERRABLE
I noticed from the 8.3 manual (CREATE TABLE section): NOT DEFERRABLE ... Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. Does this imply that custom CONSTRAINT TRIGGERs are not DEFERRABLE? Or are they? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] mssql migration and boolean to integer problems
--- On Thu, 12/13/07, robert <[EMAIL PROTECTED]> wrote: > > Hi all, I've spent the last few days hacking a > mssql INSERT script to > > work with 8.1.9 - I could build the latest postgres > source if need be. a standard cast() wouldn't work for you? proj02u20411=> select cast( 1 as boolean), proj02u20411=> cast( 0 as boolean), proj02u20411=> cast( -1 as boolean ), proj02u20411=> cast( 2 as boolean); bool | bool | bool | bool --+--+--+------ t | f| t| t Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Creating Aggregate functions in PLpgSQL
--- On Thu, 12/13/07, Jorge Godoy <[EMAIL PROTECTED]> wrote: > Actually pF is measured from -1 to 1. > > they tell you that they want doing some pF correction on > the facility and you > need to have that counted as well. Thanks for the correct, and good point. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] For the SQL gurus out there
--- On Wed, 12/12/07, Uwe C. Schroeder <[EMAIL PROTECTED]> wrote: > The comments table also has a field that holds a > self-reference to comment id > for commments on comments (on comments) of a blog. The model that you are referring to here is the adjacency list hierarchy model. There are two other models that will give you what you want but are harder to implement: path enumeration model and the nested set model. My guess is that the path enumeration model with give you what to want with a decent insert performance. Aside from this there is the ltree contribe module that take care of a lot of the work for you. > What I would like to do is to create a view that sucks the > comments for a > given blog_id in the order they should be displayed (very > commonly seen in > pretty much all blogging apps), i.e. Yup that is the problem with the adjacency list model. Regards, Richard Broersma Jr. ---(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: [GENERAL] Creating Aggregate functions in PLpgSQL
--- On Wed, 12/12/07, Martin Gainty <[EMAIL PROTECTED]> wrote: > MG>what is Xcomp? I really meant x component = effective component of power as opposed to the reactive component of power. > MG>set on stun? :o) I guess a better way to describe the problem is that total power has both a real and imaginary component. So in this way it can be liked to a complex numeric value. > MG>assume Motor Control Cubical? correct. There is a power distribution hierarchy that they would like to analyze. 1) the average pF of all motor in a Motor Control Center (MCC). 2) the average pF of all MCCs that are fed from a Load Center (LC). 3) the average pF of all LC that are fed from the facility main feed. The pF is measured between 0 and 1. 1 being purely effective power and 0 being purge reactive power. The EEs want to identify MCCs or LCs that may need Capacitor banks to help offset the effective of reactive power inherent in the inductive load of motors. This is a perfect problem for a custom aggregate. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Southern California Linux Expo
Are there any planes in the works for a booth and talks for PostgreSQL? Regards, Richard Broersma Jr. ---(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: [GENERAL] Creating Aggregate functions in PLpgSQL
--- On Tue, 12/11/07, Tom Lane <[EMAIL PROTECTED]> wrote: > Also see the overview at > http://www.postgresql.org/docs/8.3/static/xaggr.html Thanks Tom! ---(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: [GENERAL] Creating Aggregate functions in PLpgSQL
--- On Tue, 12/11/07, Matthew Dennis <[EMAIL PROTECTED]> wrote: > http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html > is for > 8.3 where there were some changes to the number of > arguments an aggregate > could take. I believe it's also more descriptive > documentation. > > of course for things like average you wouldn't need a > custom type... Thanks for the information it is very useful. The reason that I ask, is that I am trying to develop a way to calculate the average power factor(scalar) of a Motor Control Center (among other things). The Electrical Engineers that are asking for the report introduced me to a rather complicated formula to implement in standard SQL. The best way that I can describe the problem get the PF is finding the angle between Xcomp/hypotenuse after having used Pythagorean theorem to find the hypotenuse after having summing of multiple 2 coordinate vectors(the EEs use the term phasers) for each MCC cubical. It seems they have need for quite a few other little aggregate functions that they would like me to make if I can get this one done first. Anyway thank for the push in the right direction! Regards, Richard Broersma Jr. Anyway ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Creating Aggregate functions in PLpgSQL
Is it possible to create aggregate functions using pl/pgsql? If not possible in plpgsql, is there any other way to create these types of functions? If anyone could point to the correct documentation I would be most appreciative. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] if exists...does it exists for insert statments?
--- On Tue, 12/11/07, Pau Marc Munoz Torres <[EMAIL PROTECTED]> wrote: > Thanks, i'll test it tomorrow OOPS, I just noticed a mistake. INSERT INTO SP SELECT a, b FROM ( VALUES ( 'cesp', 'sp' )) AS tmp( a, b ) LEFT JOIN Sp ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b) WHERE (Sp.col1,Sp.col2) IS NULL; I forgot the parentheses that the FROM clause requires when using the VALUES predicate. Also remember that this only works in PostgreSQL versions >= 8.2. Regards, Richard Broersma Jr. ---(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: [GENERAL] if exists...does it exists for insert statments?
--- On Tue, 12/11/07, Pau Marc Munoz Torres <[EMAIL PROTECTED]> wrote:
> could i use a sentence similar to the mysql sentence
>
> insert if not exist into SP values
> ('cesp','sp');
>
> in postgresql?
Using standard ANSI-SQL the statement could be re-written:
INSERT INTO SP
SELECT a, b
FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;
Regards,
Richard Broersma Jr.
---(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: [GENERAL] SQL design pattern for a delta trigger?
--- On Mon, 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote: > but how do you > do it using SQL in an RDBMS? I believe that there is an ANSI SQL command "MERGE" that is yet to be implemented into PostgreSQL. Regards, Richard Broersma Jr. ---(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: [GENERAL] viewing definition of "CREATE TYPE name AS ENUM..."
--- On Thu, 11/29/07, Usama Dar <[EMAIL PROTECTED]> wrote: > See if they help you > http://www.postgresql.org/docs/8.3/static/functions-enum.html I will give this a try, thanks! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] viewing definition of "CREATE TYPE name AS ENUM..."
After a type is created, is it possible to view the definition of this type? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 60 Seconds to connected to Postgresql using ODBC or PGAdmin
--- On Wed, 11/28/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > Name lookups. Something is trying to look up a name, > failing and it's > timing out after 60 seconds. It seems the OP's connection string was set to localhost. Would this still indicate a Name Loopup problem? Regards, Richard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 60 Seconds to connected to Postgresql using ODBC or PGAdmin
--- On Wed, 11/28/07, Finn Lassen <[EMAIL PROTECTED]> wrote: Can anyone else comment on what problems could be causing these slow connections problems? > Meanwhile, I don't understand why it now takes exactly > 60 seconds to connect to the database whether I use pgAdmin or my > connection string from within VB. This is very odd. Since you are having a problem with your connection time on both pgAdmin(which doesn't use ODBC to connect) and ODBC connects, I would assume that you must be having an issue that is non-odbc related. > I thought I had seen a comment about this somewhere, but can't find it > now. I've tried changing Connection Pooling in the OBDC Data Source > Administrator for the PostgreSQL ANSI driver, but doesn't have any > effect (or maybe just reloading the server configuration is not enough? If this were an ODBC connection Issue, i would first make sure that all of ODBC logging was disabled on your client computer. ODBC logging can really kill performance. The windows ODBC tracing is found in the ODBC Datasource Administrator form -> tracing -> [Stop tracing now] & [Stop Visual Studio Analyzer now]. I guess it is impossible for postgresql ODBC logging to be taking place since you using a DNSless connection and have set any parameters to start the logging. If all of the logging is already off, try turning turning on the Myloging and CommLogin by setting the appropriate setting in your DNS-less connection string. If you post these logs, It will help others on the ODBC mailing list to trouble shoot where your problem is coming from. Also on a side note, it is important to remember that many of the subscribers to the Postgresql mailing list are bombarded with countless emails on a daily basis. Do to the voluminous amount of emails, I am pretty sure that most subscribers pick and choose which emails they will read purely based on the interest generated by the email's subject heading. So to help encourage more subscribes to participate, it is important to make your subject headings very specific (to the point) and to make them as eye catching as possible. You'll notice that I've alter your email subject a bit. Hopefully it will help get a few more people to join in on this thread. There is nothing wrong with tackling a very difficult but general problem with postgresql by sending seperate emails with different subject heading the specifically address only the individual facets of the overall problem. Different people will probably respond to different facets of your overall problem. Regarding the test case I sent to you, how many columns should I try to create in a table in order to reproduce the problem you where having with needing OIDs created in your tables? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Primary Key
--- On Mon, 11/26/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > In "theory" the item that would be a natural key > in this instance is the VIN. You would of course have > to make some kind of allowance for cars that don't > have a VIN (nothing in the last what... > 50 years?). So this is why the service stations always record my cars VIN number when I show up for oil changes. ;) Ofcourse, there is a whole industry built around auto theft where they restamp the stolen car with a differnt vin number. I wonder if these stolen cars end up with duplicated VIN numbers or if the VIN's they are given do not pass the the VIN check-sum (if such a think exists). Regards, Richard Broersma Jr. ---(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: [GENERAL] ODBC, access, and joins
=-- On Fri, 11/23/07, Tom Hart <[EMAIL PROTECTED]> wrote: > The problem I'm having is that anything defined as a "text" datatype in > > postgres gets converted to a "memo" datatype in access, and when we > attempt to perform a join query on thatfield access starts complaining > > about not being able to join on a field with a "memo" type. The problem is that Access doesn't know how to deal with the postgres text type. Crystal reports has the same problem. MS-Access can only create indexs on it version of the text type which is limited to 255 characters. If you know that your columns will always be less than 255 characters, then you need to define these columns as a varchar(255) so that MS-Access knows how to deal with it. Since Postgres places no limits on the length of characters that a field of the postgres text datatype can contain, Access rightly should treat it as a memo type or else there is the potential of breakage. > There's a couple other weird conversions, like boolean to text > (with a number value) but I've been able to manage most of them. I usually configure the odbc drive as [uncheck] bools as char and [check] true = -1. Ms-access seems to like this. However, there is one piece of advice that I just discovered: when dealing with odbc linked booleans fields in access, ALWAYS define these columns as not null. This is a known MS-Acess bug in that all odbc linked tables (to any flavor of RDBMS) will incorrectly treat nulls and false. All attemps to update a record using MS-access and ODBC with a null boolean will result in failure. Regards, Richard Broersma Jr. ---(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
[GENERAL] Bad Schema Design or Useful Trick?
Below I've included sample table definitions for a vertically partitioned
disjunctive table hierarchy. I wanted to point out the use of the composite
primary key declaration that is applied to two columns that are clearly not a
candidate key. However, using the badly defined primary key allows for
referential integrity to nicely handle the de-normalization between the main
table and sub tables that is inherent with this type of data model.
Would using a primary key in this manner be a decision that I will regret in
the long run? If so, can any explain why?
The parent table is parts with the child table pumps and hardware.
CREATE TABLE Parts (
part_nbr varchar( 100 ) UNIQUE NOT NULL,
part_type varchar( 20 ) NOT NULL,
unit_cost numeric(7,2) NOT NULL DEFAULT 0
CHECK( unit_cost >= 0 ),
description text NOT NULL,
CONSTRAINT parts_primary_key
PRIMARY KEY( part_nbr, part_type ),
CONSTRAINT only_defined_part_types
CHECK( part_type IN
( 'pump', 'bolt', 'nut')));
CREATE TABLE Pumps (
part_nbr varchar( 100 ) PRIMARY KEY,
part_typevarchar( 20 ) NOT NULL
CHECK( part_type = 'pump' ),
volumn real NOT NULL CHECK( volumn > 0 ),
motorhp_size varchar( 4 ) NOT NULL REFERENCES
Motortypes( motorhp_size),
CONSTRAINT parts_foreign_key
FOREIGN KEY( part_nbr, part_type )
REFERENCES Parts( part_nbr, part_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE Hardware (
part_nbrvarchar( 100 ) PRIMARY KEY,
part_type varchar( 20 ) NOT NULL
CHECK( part_type IN ( 'bolt', 'nut' ),
thread_size varchar( 4 ) NOT NULL REFERENCES
Threadtypes( Thread_size ),
grading varchar( 4 ) NOT NULL REFERENCES
Gradingtypes( grading ),
CONSTRAINT parts_foreign_key
FOREIGN KEY( part_nbr, part_type )
REFERENCES Parts( part_nbr, part_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] POLL: Women-sized t-shirts for PostgreSQL
--- On Wed, 11/21/07, Stephen Frost <[EMAIL PROTECTED]> wrote: > I've got a real desire for a PG polo. :) One which I could wear to work > (business casual or whatever) would seriously rock. That's what I like to do. The dress-code in the industry that I work is to wear collared shirts. And having a PostgreSQL polo shirt to wear certainly gives PostgreSQL a looks very professional "look" in the work place. Wearing the shirt that I have at the work place and at client facilities has started several conversations about what PostgreSQL is and how it can be used. I personally wouldn't even mind having a PG polo that has 3rd part vendor logos on the sleeves if that would help make PG polo shirts available. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] POLL: Women-sized t-shirts for PostgreSQL
--- On Wed, 11/21/07, Selena Deckelmann <[EMAIL PROTECTED]> wrote: > I have been looking into getting some woman-sized > PostgreSQL shirts, > in addition to the more typical men-sized tshirts. The Later on, would you consider doing this again for men and women sized polo shirts? I've already purchased one a while back from a business that must have gone out of business because they are no longer selling these shirts. I liked the XL shirt that I bought in every way except I that I think that the manufactures in Mexico must have different standards on shirt sizes than the US. It seems sized like a medium to large and it fits me like a glove. But there is a plus side to its sizing, I never need to iron out the wrinkles to give it that nice pressed look. :o) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] VB ADODB .Open failing
--- On Tue, 11/20/07, Finn Lassen <[EMAIL PROTECTED]> wrote:
> Here is a VB code snippet:
> Dim dbOut As ADODB.Connection
> Dim rsOut As ADODB.Recordset
> Set dbOut = New ADODB.Connection
> Set rsOut = New ADODB.Recordset
> .ConnectionString = "Driver={PostgreSQL
> ANSI};Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=none;"
> Should I have created the table "Contact1" WITH
> OIDS ?
I wonder if this is a problem with the way your ODBC driver is configured. If
it is a problem with your ODBC driver configuration, you might also try posting
to the PostgreSQL-ODBC mailing list.
Also, why did you choose the ANSI driver over the Unicode driver?
Regards,
Richard Broersma Jr.
---(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: [GENERAL] [ADMIN] "global/pg_control": Permission denied
--- On Thu, 11/15/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > My desktop (acting as a db-server for my discipline's > group) has it power cycled this morning at 4:10 >... > C:\Program > Files\PostgreSQL\8.2\bin>2007-11-15 06:36:13 > PANIC: could not open control file > "global/pg_control": Permission denied >... Does anyone have any comments on how to restore db operation after encounter this failure? Do I need to go as far have re-installing Postgres and restoring from a dump? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [ADMIN] "global/pg_control": Permission denied
--- On Thu, 11/15/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > What is the problem with the file? I looked at the pg_control file, postgres had the proper permission to it. > Why is it not accessible to Postgres? > My guess is that something (antivirus?) was messing with > the file when the system died. It seems that last night a windows service pack as added. I noticed that the global directory was marked as (grey-ed)read-only. I removed the read-only flag and postgres started up :-). I restarted my antivirus and postgres is still working. Is the global directory supposed to be marked read only? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Raid Chunk Sizes for DSS type DB
--- On Mon, 10/29/07, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > (Raid1 = No Fault tolerance since 3 drives) Raid1 with three drives will have fault tolerance. You will have three disks with the same image. This is triple redundancy. This could greatly improve select performance. Having said this, I've used software raid5 and am currently using raid10 implemented from PCI ide cards but have had data loss errors occur with both setups. I am not sure if the problem is in the drives, the pci cards, or the software raid setup. (Thank goodness that this is my toy computer.) However, I've used RAID1 with great success for my OS partitions and haven't had any problems of the last couple of years. Regards, Richard Broersma ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL and AutoCad
--- On Thu, 10/25/07, Andy <[EMAIL PROTECTED]> wrote: > >> Is there any way of converting text from an > AutoCad (.dwg ot .dxf) file into > >> a PostgreSQL Database?? > Do you want AutoCad to edit the drawings right out of the > database? How > would you want to put them in/get them out, of the > database? I think the more traditional problem is to extract information embedded (within blocks) in a drawing to produce a bill of material. As long as the text is stored in a block it is a trivial task. On the other hand, if the text is free floating in the drawing, finding it is a little more difficult but still possible using lisp or vba. Auto cad has prebuilt tools to extract/link data from blocks to any ODBC compliant database. Of course, the holy grail would be to eliminate auto cad altogether and then render drawings from the data stored in the database. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select Command
--- On Sun, 10/21/07, Adrian Klaver <[EMAIL PROTECTED]> wrote: > > I have a column with data structured as follows. > > > > 32TT - 0002 > > 32LT- 0004 > > 32PT-0005 > > > > Is there a way of selecting all of the rows containing > LT in that column?? > > > > I have attempted variations of ' *LT* ' with > out success. I set up my table structure a little differently: CREATE TABLE loops ( area INTEGER CHECK( AREA BETWEEN 1 AND 99), process_code VARCHAR(1) CHECK ( process_code ~ [A-Z]), loop_nbr INTEGER CHECK( loop_nbr BETWEEN 1 AND ), suffix VARCHAR(1) CHECK ( suffix ~ [A-Z] ), loop_work_scope VARCHAR CHECK ( loop_work_scope IN ( 'NEW','REUSE','DEMO'), PRIMARY KEY ( area, process_code, loop_nbr, suffix, loop_work_scope) ); CREATE TABLE tags ( area INTEGER CHECK( AREA BETWEEN 1 AND 99), process_code VARCHAR(1) CHECK ( process_code ~ [A-Z]), loop_nbr INTEGER CHECK( loop_nbr BETWEEN 1 AND ), suffix VARCHAR(1) CHECK ( suffix ~ [A-Z] ), loop_work_scope VARCHAR CHECK ( loop_work_scope IN ( 'NEW','REUSE','DEMO'), Foriegn KEY ( area, process_code, loop_nbr, suffix, work_scope) REFERENCES ( Loops.area, Loops.process_code, Loops, Loop_nbr, Loops_suffix, Loops.work_scope ), Instrument_code VARCHAR(4), Instrument_suffix varchar(1) check ( suffix ~ [A-Z]) Instrument_work_scope varchar check (insturment_work_scope in ( 'NEW','REUSE','DEMO'), Primary KEY ( area, Instrument_code, loop_nbr, instrument_suffix, Instrument_work_scope ) ); SELECT area || insturment_code || to_char( loop_nbr, '' ) || instrument_suffix AS tag FROM Tags WHERE instrument_code = 'LT' AND instrument_work_scope = 'NEW'; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] uniquely indexing Celko's nested set model
--- On Sat, 10/20/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > Have you eliminated other approaches, such as arrays, ltree, etc? Actually I haven't considered using arrays to store hierarchal information. I've seen ltree mentioned from time to time. Is it true that it works with adjacency list model? If the nested set model is chosen, would having a table and index fill factor of 50% be a good idea in this case if periodic updates were expected? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] uniquely indexing Celko's nested set model
--- On Fri, 10/19/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > Is it possible to constraint both the LEFT and RIGHT > > fields of a record to use the same index? I am looking for > > a way to ensure for all LEFTs and RIGHTs in a table, that is > > it is impossible for any LEFT or RIGHT to have to same > > value. > a check constraint ought to do it True, but how do I insure that one record's left does not equal another record's right? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] uniquely indexing Celko's nested set model
Is it possible to constraint both the LEFT and RIGHT fields of a record to use the same index? I am looking for a way to ensure for all LEFTs and RIGHTs in a table, that is it is impossible for any LEFT or RIGHT to have to same value. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected
--- "John D. Burger" <[EMAIL PROTECTED]> wrote: > I read this as: Find all parents such that none of their kids are > girls and none of their kids are boys. That is, ALL of their genders > are <> 'girl', AND ALL of their genders are <> 'boy'. Under the > obvious assumptions about gender, the result is of course empty - > except it's not clear to me what should happen for childless people ... Thanks everyone that makes sense! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected
I expect that my intuition is incorrect about the use of ALL() and ANY(), but I
found my result to
be reverse from what I expected.
Can anyone explain why <> ANY() behaves that way it does?
Here are two test case examples that do what I expect:
-- find all parent that only have boys
SELECT *
FROM Parents AS P
WHERE 'boy' = ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
-- find all parent that only have girls
SELECT *
FROM Parents AS P
WHERE 'girl' = ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
Here is the example that doesn't do what I expect:
--find all parents that have a mixture of boys and girls.
--but this doesn't return anything
SELECT *
FROM Parents AS P
WHERE 'girl' <> ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid )
AND 'boy' <> ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
--This query gets what I want
--but I believe that it shouldn't return anything
SELECT *
FROM Parents AS P
WHERE 'girl' <> ANY ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid )
AND 'boy' <> ANY ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
Regards,
Richard Broersma Jr.
---(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: [GENERAL] Lifting WHERE conditions out of inner select
OOPs! --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > --- "John D. Burger" <[EMAIL PROTECTED]> wrote: > > My question is, should the planner have figured this out, and we're > > just losing out because we're stuck in 7.4? Or is there some subtle > > difference in semantics I'm missing? The select results were the > > same in both cases, but I'm willing to believe that's an accident of > > our data. > > I don't know if this helps. > > SELECT lemma > FROM word AS W > INNER JOIN sense AS S > ON W.wordid = S.wordid > INNER JOIN semlinkref AS R > ON R.synset1id = S.synsetid >AND R.synset2id = S.synsetid > INNER JOIN synset AS T > ON S.synsetid = T.synsetid > WHERE W.lemma = 'scramble' >AND R.linked = 1 >AND T.pos='v' > ORDER BY lemma; > > Regards, > Richard Broersma Jr. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Lifting WHERE conditions out of inner select
--- "John D. Burger" <[EMAIL PROTECTED]> wrote: > My question is, should the planner have figured this out, and we're > just losing out because we're stuck in 7.4? Or is there some subtle > difference in semantics I'm missing? The select results were the > same in both cases, but I'm willing to believe that's an accident of > our data. I don't know if this helps. SELECT lemma FROM word AS W INNER JOIN sense AS S ON W.wordid = S.wordid INNER JOIN semlinkref AS R ON R.synset1id = S.synsetid AND R.synset2id = S.synsetid INNER JOIN synset AS T ON S1.synsetid = T.synsetid WHERE W.lemma = 'scramble' AND R.linked = 1 AND R.pos='v' ORDER BY lemma; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off
--- "A. Kretschmer" <[EMAIL PROTECTED]> wrote: > > > at least post them and see what kind of response you get, > > > rather than judge the list as a whole due to the response you got to > > an > > > off-topic post. Many of the people on the lists have been here for > > years and > > > have gotten lots of helpful advice, which is why they've stuck > > Can you please see this shit of broken lines? This is from YOU! I use yahoo. I guess that yahoo's web-based client must be broken also, since I've seem email I've posted has produce broken lines. I am not sure if this was already mentioned, does anyone know of non-html windows email clients that work well for this mailing list? Regards, Richard Broersma Jr. ---(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: [GENERAL] 3 tables join update
--- rihad <[EMAIL PROTECTED]> wrote: > UPDATE Foo foo > SET foo.baz_id=baz.id > FROM Baz baz LEFT JOIN Bar bar ON (foo.bar_id=bar.id) > WHERE bar.id IS NULL; This query cannot work. Basiclly, you are trying to set the foo.baz_id = baz.id for records in foo that do not yet exist. Doing this is impossible. For this to work, you should be using an INSERT query to _ADD_ records to foo that do not yet exist. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] decode access privileges
--- John Smith <[EMAIL PROTECTED]> wrote:
> what does this mean?
> {postgres=arwdRxt/postgres,username=r/postgres}
This link describes each of the letters:
http://www.tldp.org/LDP/intro-linux/html/sect_03_04.html
Regards,
Richard Broersma Jr.
---(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: [GENERAL] Why the ERROR: duplicate key violates uniqueconstraint "master_pkey" is raised? - Is this a Bug?
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Isn't this expected behavior? When you update the m2 of the first > record, it becomes 2 and violates the unique constraint as the second > row already has an m2 value of 2. Well, it is a limitation PostgreSQL. This type of update is should work without any problem according to the SQL standard. The Problem with performing two updates is that it double the amount of dead tuples. I guess that this would be another example where having a small fill factor would help. One kind of data model that depends heavily on this type of operation is the Hierarchical Nested Set data model. Inserting/updating/deleting nodes and branches into the table requires updating the primary key of a lot of records. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Windows Auto-Vacuum in 8.2.4 or 8.2.5
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > I will dis-able the hourly manual vacuum/analyze script that I implemented as > a work-around to > this problem to see if auto-vacuum is ever triggered. it appears to be working fine in 8.2.5: proj02u20411=> begin transaction; BEGIN proj02u20411=> update document set docdisc = docdisc; UPDATE 7927 proj02u20411=> update document set docnum = docnum; UPDATE 7927 proj02u20411=> rollback; ROLLBACK proj02u20411=> explain analyze select count(*) from docs.document; QUERY PLAN - Aggregate (cost=641.58..641.59 rows=1 width=0) (actual time=25.340..25.341 rows=1 loops=1) -> Seq Scan on document (cost=0.00..587.26 rows=21726 width=0) (actual time=0.025..14.578 rows=7927 loops=1) Total runtime: 25.508 ms (3 rows) proj02u20411=> \x Expanded display is on. proj02u20411=> SELECT * proj02u20411-> FROM Pg_stat_all_tables proj02u20411->WHERE schemaname = 'docs' proj02u20411-> AND relname = 'document' proj02u20411-> ORDER BY schemaname, relname; -[ RECORD 1 ]+--- relid| 16867 schemaname | docs relname | document seq_scan | 4769 seq_tup_read | 34099275 idx_scan | 452899 idx_tup_fetch| 1679315 n_tup_ins| 59 n_tup_upd| 15868 n_tup_del| 52 last_vacuum | 2007-09-21 07:00:03.646-07 last_autovacuum | 2007-09-21 07:57:49.496-07 last_analyze | 2007-09-21 07:00:03.646-07 last_autoanalyze | 2007-09-21 07:57:49.496-07 proj02u20411=> \x Expanded display is off. proj02u20411=> explain analyze select count(*) from docs.document; QUERY PLAN - Aggregate (cost=234.09..234.10 rows=1 width=0) (actual time=22.014..22.016 rows=1 loops=1) -> Seq Scan on document (cost=0.00..214.27 rows=7927 width=0) (actual time=0.025..11.490 rows=7927 loops=1) Total runtime: 22.140 ms (3 rows) proj02u20411=> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Windows Auto-Vacuum in 8.2.4 or 8.2.5
--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Maybe you should restate your problem so we can try to discover the > cause. Okay. I will dis-able the hourly manual vacuum/analyze script that I implemented as a work-around to this problem to see if auto-vacuum is ever triggered. But to start off with, here are the settings that I currently have in my postgresql.conf for auto-vacuum. I will post-back with any results whether I see auto-vacuum working in 8.2.5 or not. Thanks! Regards, Richard Broersma Jr. #--- # RUNTIME STATISTICS #--- # - Query/Index Statistics Collector - stats_start_collector = on # needed for block or row stats # (change requires restart) stats_row_level = on #--- # AUTOVACUUM PARAMETERS #--- autovacuum = on # enable autovacuum subprocess? # 'on' requires stats_start_collector # and stats_row_level to also be on autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 100 # min # of tuple updates before # vacuum autovacuum_analyze_threshold = 25 # min # of tuple updates before # analyze autovacuum_vacuum_scale_factor = 0.002 # fraction of rel size before # vacuum autovacuum_analyze_scale_factor = 0.001 # fraction of rel size before # analyze autovacuum_freeze_max_age = 2 # maximum XID age before forced vacuum # (change requires restart) autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Windows Auto-Vacuum in 8.2.4 or 8.2.5
--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Hmm, maybe it was, but then I cannot recall what bug I was referring to > :-( I can't find a relevant CVS log message either. Okay. FWIW, I am still seeing the same behavior in Version 8.2.4. But I am about to upgrade to 8.2.5, and I will check to see if I notice a difference. Ofcourse, maybe auto-vacuum does work but I have a configuration error some where. Thanks for the consideration! Regards, Richard Broersma Jr. ---(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: [GENERAL] Windows Auto-Vacuum in 8.2.4 or 8.2.5
I thought I would give this question a second try. --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > A while back it was pointed out the that the Windows version of 8.2.3 had a > bug that prevented > auto-vacuum from working correctly. > > http://archives.postgresql.org/pgsql-general/2007-04/msg00139.php > > I wasn't able to determine from the release notes if this bug was fixed in > versions 8.2.4 or > 8.2.5. Does any know if it was? > > Regards, > Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
--- Erik Jones <[EMAIL PROTECTED]> wrote: > Also, note that once we have HOT... I am not sure what the acronym "HOT" stands for. Does it have something to do with MVCC? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > 2. Is this fill factor enough to have on the table, or should I also > do a fill factor for specific indexes? Or both the table and the > index? (I have four btree indexes on the table) I don't think that fill factor can be applied to the table. The CREATE TABLE reference doc show that fill factor can be used in the CREATE TABLE statement, but it is only applied to syntax that creates an implied index. i.e. CREATE TABLE test ( test_idINTEGER PRIMARY KEY WITH ( FILLFACTOR = 70 ), test_val TEXT ); Primary key will create an implied index. Fill factor is applied to that implied index. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
> Not quite. Once a page has reached it's fill factor percentage full, > no more inserts will happen on that page, only updates. Also, I > think you have large/small backwards wrt fill factor. If you have a > fill factor of, say, 40% then once a page has reached 40% full no > more inserts will happen (unless some space is reclaimed by vacuum). > So, smaller fill factors == bigger holes. The bigger the fill > factor, the smaller the whole: if you have a fill factor of 90%, > only 10% is reserved for updates of rows on that page. So (just to reiterate), fill factor can be applied to both a table and/or an index(es). But the "holes" built into the page of a table or index can only be filled by UPDATE Statements. Thanks for the clarification! Regards, Richard Broesma Jr. ---(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: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Then I am confused again about how the fill factor stuff works. Let's > say I have a table with four BTREE indexes. Should all of them have a > fill factor of about 60 (lower than the default 90, that is) to be > effective? Or will it help if I lower the fill factor on only a couple > of the most often used ones? The primary key index is very, very > rarely updated so I don't need it to have a fill factor. > > I could try and see these one by one, but that's no better than > touching/feeling the database blind-folded. I would love to see some > writeup about this whole thing, but it seems hard to come by! I will try to explain everything that I understand about indexes and tables. I am sure that if some part of my understanding is incorrect, someone will chime in to correct me. In PostgreSQL, tables are physically distinct from indexes. This means that any give table is written to disk as a file(s), and indexes are also written to disk as a separate file(s). A table and index are both saved to disk in segmented block referred to a pages (I believe the default size is 8K). The advantage of the index file is that it is significantly smaller in size, so it takes less time to sequentially scan and less time to read from disk. Now when you want to find a record, PostgreSQL will/(may choose to) sequentially scan the index until it find the record entry that corresponds with your criteria. This entry has a table cross-reference to the actual page that contains the record that is "pointed" at by the index. Lastly, the entire table page containing your record is read from disk to memory for further query processing. When you insert a record into a table that generates an entry into the b-tree index file, PostgreSQL will scan the pages of the index file to find the correct place and index page to add this entry. If the page is already full, PostgreSQL "probably" replaces the old full pages with two new pages with a distribution of that chunk of the B-tree index, and then adds the new entry to one of those pages. This operation can become very expensive if many new pages need to be created from single INSERT/UPDATE statement. By using fill factor, you are telling PostgreSQL to automatically leave a portion of any newly created index page partially blank for future use. When a newly created index entry needs to be saved, it can be stored in one of the "holes" left in the index page. A large fill factor create both advantages and dis-advantages. For writing operations, it is a big advantage because, a large fill factor will leave alot of holes for INSERT and UPDATE operations to use. This can help increase the number of UPDATE/INSERT per seconds that you server can handle since, they index pages do not have to be rebuilt very often. However, the disadvantage is that, a newly created index with a large fill factor has "index bloat" designed into it. This mean that the index pages have a large portion of holes. So PostgreSQL will create more index pages than it would normally in order to hold both your index and the pre-defined holes that you specified by your fill-factor. Larger indexes require more time to be read from disk to memory and will require more time to sequentially scan to find to find the cross-reference table page location of records of interest. So the net effect is that larger indexes will make SELECT statement slower. This is my understanding for tables indexes and fill factor. I hope it helps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
--- Gregory Williamson <[EMAIL PROTECTED]> wrote: > A very low fill factor means that pages are > "sparse" and so inserts and updates are less likely to trigger massive b-tree > rebalancings. I take it that "massive b-tree rebalancings" could cause a problem with the performance of disk writing though-put from UPDATEs and INSERTs? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Windows Auto-Vacuum in 8.2.4 or 8.2.5
A while back it was pointed out the that the Windows version of 8.2.3 had a bug that prevented auto-vacuum from working correctly. http://archives.postgresql.org/pgsql-general/2007-04/msg00139.php I wasn't able to determine from the release notes if this bug was fixed in versions 8.2.4 or 8.2.5. Does any know if it was? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ON INSERT => execute AWK/SH/EXE?
--- "A. Kretschmer" <[EMAIL PROTECTED]> wrote: > You can do this, you need a untrusted language like plperlU or plsh. > Then you can write a TRIGGER and call external programs. This may be a silly question, will plsh work on a windows server? I am pretty sure that plbat doesn't exist :-). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > What constitutes a "small fill factor"? Would 70 be good? I guess my > current must have been the default, which the manual says is 100. On the following link: http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS I found this: "B-trees use a default fillfactor of 90, but any value from 10 to 100 can be selected." Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Cannot declare record members NOT NULL
--- Cultural Sublimation <[EMAIL PROTECTED]> wrote: > > I don't know if this will work, but here is another idea: > > > > SELECT movie_id, movie_name > > FROM get_movies() AS ( int4 NOT NULL, text NOT NULL ); > > > > Hi, > > Nope. That's not even valid syntax... It isn't valid SQL spec syntax but it is a Postgresql-ism for functions return types: http://www.postgresql.org/docs/8.2/interactive/sql-select.html notice this "from-type" listing for functions. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cannot declare record members NOT NULL
--- Cultural Sublimation <[EMAIL PROTECTED]> wrote: > > CREATE FUNCTION get_movies () > > RETURNS SETOF get_movies_t > > LANGUAGE sql STABLE > > AS > > $$ > > SELECT movie_id, movie_name FROM movies > > WHERE movie_id NOT NULL AND movie_name NOT NULL; > > $$ > > > SELECT movie_id, movie_name FROM get_movies (); > => returns a SETOF of (int4 NULL, text NULL) I don't know if this will work, but here is another idea: SELECT movie_id, movie_name FROM get_movies() AS ( int4 NOT NULL, text NOT NULL ); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Event-driven programming?
--- Jay Dickon Glanville <[EMAIL PROTECTED]> wrote: > Is it possible for PostgreSQL to notify me of a successful transaction commit? I've haven't used it yet, but will LISTEN and NOTIFY work for you? http://www.postgresql.org/docs/8.2/static/sql-listen.html http://www.postgresql.org/docs/8.2/static/sql-notify.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Partial index with regexp not working
--- Richard Huxton <[EMAIL PROTECTED]> wrote: > Phoenix Kiula wrote: > > CREATE INDEX idx_trades_tid_partial ON trades (trader_id) > > WHERE trader_id ~ '[a-z]' ; > > >WHERE trader_id = 'johndoe' > > > > It is not using this index at all! It is using no index in fact, it's > > trying to do a sequential scan. Any ideas why this partial index is > > not working?? > A partial index will only be considered if you test for its condition: > > SELECT ... WHERE trader_id = 'johndoe' AND trader_id ~ '[a-z]' IIRC, for any index like this to work, doesn't the REGEXP need to be anchored to either the start or end of the string? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Statistics collection question
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > LOG: duration: 93473.282 ms statement: select t_info, dstats, id > from trades where t_alias = '17huv' and status = 'Y' > > --- > > Index Scan using trades_unique_t_alias on trades (cost=0.00..3.41 > Time: 2.990 ms Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select statement times? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Export data to MS Excel
--- Ashish Karalkar <[EMAIL PROTECTED]> wrote: > Hello All, > I want to export data from PostgreSQL tables to MS Excel. > Is there any way? Excel has the ability to directly pull data from any system ODBC DSN configured on the windows box. Was is nice with this method is the ability for the users to "Refresh" the exported data directly from excel using the build in functionality of excel. http://pgfoundry.org/projects/psqlodbc/ http://www.sls.psi.ch/controls/help/tutorials/Excel_ODBC/index.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
--- Tony Caduto <[EMAIL PROTECTED]> wrote: > Check it out here: > > http://www.amsoftwaredesign.com/pg_vs_fb One row that you could elaborate on is: CHECK CONSTRAINTS support for correlated sub-queries. PostgreSQL doesn't official support this kink of constraint unless it is rolled up in a function. I am not sure what support FB has for this. Another Constraint row you could add would be: CREATE ASSERTION which is a schema level constraint. Currently PostgreSQL doesn't support this, I am not sure if FB does either. Also you could mention PostgreSQL support for row-wise comparison: i.e. WHERE ( last_name, city, gender ) = ( 'Doe', 'Paris', 'female' ); and PostgreSQL support for additional SQL comparison operators: i.e. WHERE (( last_name, city, gender ) = ( 'Doe', 'Paris', 'female' )) IS UNKNOWN; -- return all people who might meet this criteria if their null field where known. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Table description
--- Livia Santos <[EMAIL PROTECTED]> wrote: > Is there any command that describe a table, such as desc table_name as in > Oracle? from the psql user interface, you can use the following command: \dt to list all table in the currently set schema \d [table_name] the see the table attributes \d+ [table_name] to see additional table attributes Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Fastest way to import only ONE column into a table? (COPY doesn't work)
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > UPDATE T1 > >SET T1.title = T2.title > > FROM T2 > > WHERE T1.id = T2.id > >AND T1.title IS NULL; > > Thanks much RIchard, but neither of those work. For me table t1 has > over 6 million rows, and table t2 has about 600,000. In both of the > queries above I suppose it is going through each and every row of > table t1 and taking its own sweet time. I've dropped all indexes on > t1, but the query has still been running for over 45 minutes as I > write! Any other suggestions? You could post the explain plan for this above query to verify your theory. Also it might give ideas about whether carefully placed indexs would help. My guess is that you will need to add indexes at least on T1.id and T2.id to help reduce the join time. Also, since you mention that you already attempted this query, I would view the postgresql-logs to see if you might need to increase your check-point size temporarily just for this operation. Also, since this is hopefully a one time update, you could temporarily turn off fsync during the update. when the update is done, you should turn fsync back on. My guess is that a join and update on 6 million records in just going to take a while. Hopefully this isn't an operation that you will need to preform regularly. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Customizing psql console to show execution times
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > In some examples posted to this forum, it seems to me that when people > execute queries in the psql window, they also see "90 ms taken" > (milliseconds), which denotes the time taken to execute the query. > Where can I set this option because I'm not seeing it in my psql > window on both Win XP and Linux. Thanks! You can get the timing from psql two ways: EXPLAIN ANALYZE your_query; Or you can use of the psql internal commands to see the this of psql internal command type "\?" from the psql command prompt. The one you want is "\timing". However, notice that "\timing" and explain analyze do not exactly agree on the results they produce. IIRC, "\time" also counts its own overhead. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Yet Another COUNT(*)...WHERE...question
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Sorry I was not clear. Imagine an Amazon.com search results page. It > has about 15 results on Page 1, then it shows "Page 1 of 190". I don't think that amazon or google really need to give an accurate count in determining an estimated number of pages... Could you determine the number of pages quickly from postgresql: [ row count estimate ] / [ number of rows you want per page] The estimated row count is updated every time you vacuum your tables. And getting the estimate takes very little time. > To show each page, the query probably has a "LIMIT 15 OFFSET 0" for > Page 1. The "LIMIT 15 OFFSET 1500" technique can be a performance killer since offset does not use an index. Is is better to use the last entry of each page in the query for the next page, so you can write your query this way: SELECT * FROM your_table WHERE item_nbr > [: last item on previous page :] ORDER BY item_nbr LIMIT 15; This method was discuss on the list a couple of months ago. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Yet Another COUNT(*)...WHERE...question
--- Scott Marlowe <[EMAIL PROTECTED]> wrote: > Generally, for these kinds of things it's often best to use > materialized views / rollup tables so that you aren't re-aggregating > the same data over and over. I don't know if this was already mentioned, but here is one of the links that describe the method of implementing a materialized view. http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html other useful docs like this one can be found here: http://www.postgresql.org/docs/techdocs.2 Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] can i use an array as a table (in the from clause)
--- Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > Is there some way that I can treat a two dimensional array as a table > > that can be referenced in the from clause? > > I know of no way off hand, and if so, not easily. This is a pretty > clear sign that you shouldn't be using arrays in this context and > should rethink your schema. The closest thing to an array (but is actually a derived table) is achieved using the VALUES predicate. SELECT col1, avg( col2 ) FROM ( VALUES ( 1, 1 ), ( 1, 2 ), ( 2, 3 ), ( 2, 4 )) AS ValuesTable( col1, col2 ) WHERE col2 < 5 GROUP BY col1 ORDER BY col1; col1 |avg --+ 1 | 1.5000 2 | 3.5000 (2 rows) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Timestamp in pg_dump output, was Re: [GENERAL] How I can know a back up database is up to date
--- Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > On 09/08/2007 23:40, [EMAIL PROTECTED] wrote: > > > My database is restored from a dump file every day. How I know that this > > database is up to date (as it has no timestamp in any table). > > > > If I create a file, I can know when I created it by seeing its property. > > How I can do the same thing with a back up database. > > Actually, it *would* be really handy if pg_dump included a timestamp in > the plain-text output. The version I use regularly (Windows) > doesn't...it simply says "PostgreSQL database dump" which is only > helpful to a point. :-) If you need to, you can append your own timestamp to the dump file if you need it. I rolled this functionality into a .bat file. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plperl syntax highlighting for vi
> > http://www.gunduz.org/postgresql/pgsql.vim > > That's great for PL/PgSQL. Maybe there's a way to do it for PL/Perl, > too :) Can anyone point me to a link that shows which file vim conf file need to be configured and how to configure it to use this above .vim file? TIA, Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL, PGDAY, PGParty and OSCON 2007 Rocked!
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > Hello, > > For those who were too square to be there, For us squares, are there any pictures avaliable of this event? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres Geometry
--- Bob Pawley <[EMAIL PROTECTED]> wrote: > I have developed a PostgreSQL database c/w a Delphi interface with which to > input data. > If so, are there any tools that may assist me in developing this graphic > interface? This link seemed enteresting to me. http://www.netbeans.org/download/flash/netbeans_60/jl_preso_vislib/player.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Trigger Priority
--- Mike Gould <[EMAIL PROTECTED]> wrote: > Is there a way to determine the order that triggers are executed? We are > converting from SQL > Anywhere 9 and one of the features that they have is the ability to add a > order statement to > triggers. This allows from a programmatic way to control how triggers are > fired if there > is more than one trigger for a particular type (before, after) the insert, > update, delete > commands. The triggers fire in alphbetical order. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Looking for help regarding getting the latest inserted sequence value.
--- Magnus Hagander <[EMAIL PROTECTED]> wrote:
> If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
> INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey
>
> with pkey being the SERIAL field.
I don't want to derail the thread too much, but would it be nice if the
returning could be used in
a insert sub-query?
INSERT INTO Childtable ( parentfkey, childname )
VALUES ( ( INSERT INTO Parenttable ( parentname )
VALUES ( 'FRED' ) RETURNING pkey ), 'Jed');
This with one statement, you could insert into two or more tables.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Checking for a number
--- Osvaldo Rosario Kussama <[EMAIL PROTECTED]> wrote:
> Try:
> SELECT your_field ~ '.*[[:digit:]]{2}$';
This could be simplified a little. :o)
WHERE your_field ~ '\\d{2}$';
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] View Triggers
It seems that insertion triggers on views will/may be added in version 8.3. http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php However, Tom mentioned that adding update and deletion triggers may be a bad idea: http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php ... However, I'm not sure that the idea scales to cover updates and deletes; with no concept of physical tuple identity (ctid) for the view rows, it's not clear that you can write triggers that will reliably do the right things. ... I am curious about the dangers could occur if UPDATE triggers were implemented on a view? I assume that these dangers are most apparent with views that are based upon joined tables rather than views on a single table. Do these dangers also apply to update DO INSTEAD rules? Could there exist any view design guidelines that if followed would allow for the safe utilization of update triggers? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
