Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On Thu, 2002-11-21 at 15:09, scott.marlowe wrote: > On 21 Nov 2002, Rod Taylor wrote: > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > > Of course, those would be SQL purists who _don't_ understand > > > concurrency issues. ;-) > > > > Or they're the kind that locks the entire table for any given insert. > > Isn't that what Bruce just said? ;^) I suppose so. I took what Bruce said to be that multiple users could get the same ID. I keep having developers want to make their own table for a sequence, then use id = id + 1 -- so they hold a lock on it for the duration of the transaction. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > Of course, those would be SQL purists who _don't_ understand > concurrency issues. ;-) Or they're the kind that locks the entire table for any given insert. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [PERFORM] 7.3.1 index use / performance
> I am wondering about a compiler bug, or some other peculiarity on your > platform. Can anyone else using FreeBSD try the above experiment and > see if they get different results from mine on 7.3.* (or CVS tip)? On FreeBSD 4.7 I received the exact same results as Tom using the statements shown by Tom. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] [HACKERS] Please include hier-patch in next PostgreSQL version
On Mon, 2003-01-27 at 10:05, Boris Klug wrote: > Hello! > > I want to say that it would be fantastic when you include the Oracle like > "CONNECT BY" patch (see gppl.terminal.ru/readme.html) in the next version of > PostgreSQL. > It is very usefull for people that have to handle such kind of hierarchical > data. Yes, very useful -- but use the SQL 99 syntax for recursive queries and not the wonky Oracle version. It's more flexible. Looking forward to the patch ;) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Denormalizing during select
On Tue, 2003-02-25 at 13:48, Edmund Lian wrote: > On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote: > > >I found this example in "Practical PostgreSQL"... will it do the job? > > Answering my own question: kind of. The problem with custom aggregates > is that they need to be used with a "group by" clause, and this means > that the select cannot return columns that are not aggregates of some > kind. What I'm trying to return are rows that are a combination of > columns and aggregates. I've been trying to figure out how to give a running total (similar issue I think). key value 1 5 1 5 1 5 2 1 2 2 2 1 Query output: key value sum to point 1 5 5 1 5 10 1 5 15 2 1 1 2 2 3 2 1 4 I think I should be able to do it with an aggregate -- but the best I've been able to come up with is a Set Returning Function. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Denormalizing during select
On Tue, 2003-02-25 at 13:48, Edmund Lian wrote: > On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote: > > >I found this example in "Practical PostgreSQL"... will it do the job? > > Answering my own question: kind of. The problem with custom aggregates > is that they need to be used with a "group by" clause, and this means > that the select cannot return columns that are not aggregates of some > kind. What I'm trying to return are rows that are a combination of > columns and aggregates. I've been trying to figure out how to give a running total (similar issue I think). key value 1 5 1 5 1 5 2 1 2 2 2 1 Query output: key value sum to point 1 5 5 1 5 10 1 5 15 2 1 1 2 2 3 2 1 4 I think I should be able to do it with an aggregate -- but the best I've been able to come up with is a Set Returning Function. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Atomicity of UPDATE, interchanging values in unique column
> UPDATE sometable SET unique_col = >CASE WHEN unique_col = firstvalue THEN secondvalue > ELSE firstvalue >END > WHERE unique_col = firstvalue > OR unique_col = secondvalue (See last comment) > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > > UPDATE sometable SET unique_col = firstvalue WHERE unique_col = secondvalue; > UPDATE sometable SET unique_col = secondvalue WHERE unique_col = firstvalue; > > COMMIT; This one will always fail unless you DEFER unique constraints -- something we don't support with PostgreSQL, but some others do. > How can I interchange two values in a unique column? Am I missing something > really > obvious (like a swap statement)? Is there any reason besides performance for > not > making index accesses fully ACID-compliant? Doesn't MVCC require this > anyway? The first is what you want. PostgreSQL needs some work in the evaluation of unique indexes to properly support it. Namely, when it sees a conflict when inserting into the index, it needs to record the fact, and revisit the conflict at the end of the command. Lots of work... -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Atomicity of UPDATE, interchanging values in unique
On Sat, 2003-03-08 at 16:48, daniel alvarez wrote: > > The first is what you want. PostgreSQL needs some work in the > > evaluation of unique indexes to properly support it. > > > > Namely, when it sees a conflict when inserting into the index, it needs > > to record the fact, and revisit the conflict at the end of the command. > > Lots of work... > > OK. The long-term goal would then be to get rid of such oddities. But what > can I do right now as a user to solve that issue for my application? Certainly.. But you have to find someone willing to do a the work for little gain. There are lots of issues more important to most of the developers. > There must be a better solution than the additional dummy update. You could try hiding it behind a function, but I'm afraid thats the only sane way to do it. Select into temp table, delete both, and insert values back in again is another :) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Cursors and backwards scans and SCROLL
> I'm presently leaning to #2, even though it exposes implementation > details. I'm open to discussion though. Any preferences? Other ideas? How about a variable that turns on or off spec enforcement (case #1 or #2). On for 7.4, off for 7.5 the next release, and make it disappear after that. Enforcing spec seems like the least confusing mode to operate under, especially given it could break simply by changing the plan -- which happens automagically (seemingly random). -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Sorting by NULL values
On Tue, 2003-03-04 at 15:13, Stephan Szabo wrote: > On Tue, 4 Mar 2003, Ian Burrell wrote: > > > I am doing a query where I need to sort by a column that may be NULL > > because it is coming from an OUTER JOIN. I noticed a difference between > > PostgreSQL and other databases about where NULLs show up. It seems that > > with Postgres, NULLs are sorted after other values. Other databases > > sort them before. > > > Is there any standard on how sorting NULLs work? Is there a way to If you care, order by their boolean equivelent first: order by field is null desc, field DESC puts nulls first, since true > false > IIRC, they're either considered greater than or less than non-NULL values, > but the decision is up to the implementation. > > > change Postgres's behavior? Is there a way to replace the NULLs with > > empty strings? > > Coalesce should work. > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] [PHP] faster output from php and postgres
On Tue, 2003-05-27 at 14:19, Richard Huxton wrote: > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > > So, I have the same problem, but I need all authors for each publication > > to show up in it's own column. I tried the full join query from a > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > > of ALL authors at once. > > > > I'll do some RTFMing of the joins.. and post any results I get > > > > BUT, right now, looping over each publication with php isn't taking that > > long. I would like to know how to make this query, though! > > > > Please let me know how to get a result like: > > > > > > |All Authors|Title|Source|Year|Type|Length|Keywords| > > Well, if you search the archives for terms "text", "concat", "aggregate" you > should come up with one solution. This involves writing your own aggregate > function, like SUM() but for text. Don't worry, it's not difficult. The only > issue is that you won't be able to guarantee the order of authors in the > field. If order is required: SELECT custom_aggregate(author) as authors FROM (SELECT author FROM table ORDER BY author) AS tab; The above should give you authors in alphabetical order if custom_aggregate() was written to concatenate text. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Upgrade 7.2.3 -> 7.3 or more
On Tue, 2003-05-27 at 08:44, Benoît Bournon wrote: > I have to use PREPARE statement, I have just read an article that this > function is not implemented in 7.3 version ? It's implemented, however how it works internally will change somewhat in 7.4. http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-prepare.html -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] generic return for functions
> thing that causes me some minor grief is the fact that currently you > cannot have default values to function parameters, a feature we use a > lot. The default value is used when the parameter is NULL or unprovided? fn(integer, integer, integer default 32) select fn(integer, integer); <- Third argument would be '32'? When PostgreSQL gets named parameters the above probably makes sense to add. A TODO item? -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] (long) What's the problem?
> rposition() is a volatile custom C function. Does the query function as expected when not being EXPLAINed ? Odds are it's a bug in the custom C function. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Domains and Joins
On Thu, 2003-06-05 at 17:38, chester c young wrote: > -- standard setup: > create table t1( c1 int primary key, data text ); > create domain dom_c1 int references t1 on delete cascade; This won't work. Domains support NOT NULL, and CHECK constraints. Foreign keys are not allowed on domains at this time in PostgreSQL or in the SQL99 spec. > create table t2( c2 int primary key, c1 dom_c1, moredata text ); > -- will not work with "using" > create view v1 as select t1.*, t2.moredata > from t1 join t2 using( c1 ); It appears to be a complaint about mis-matched datatypes, which is partially true. It's also a problem in 7.4. I'll see what I can do to fix it. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Column limits in table/ views
On Fri, 2003-06-06 at 18:02, A.M. wrote: > I have 560 columns of NUMERIC(10,14). To not run up against max column > restraints, I split the information into two tables. Does the column > limit on tables imply the same limit for views or selects or could I > potentially select a row across both tables and make a view that hides > the split? The limit is documented as having to do with the size of a block. The largest a single row can become is 8k with primitive types (text, and other variable length types will 'overflow' into another area -- see TOAST). Selects should be able to return more columns than that. Both views and tables create a ROW TYPE to represent it, which is where the limitation is. Thus, I would not expect you to be able to create a view. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Coalesce/Join/Entries may not exist.
On Mon, 2003-06-09 at 08:00, James Taylor wrote: > I've got three tables, I'll shorten the columns down just so you get the > idea: > > lists > --- > id|order_id > > list_results > > id|lid|total > > orders > > id|max > > All of the columns are int's. What I'm trying to do is something like: > > select (o.max-coalesce(sum(lr.total),0)) from orders o,list_results lr > where lr.l_id in (select >l.id from lists l, orders o where l.order_id=X and o.id=l.order_id) > group by o.max > > This would, in theory, return a number which should be > o.total-sum(lr.total) > > The problem is, there may not be any data in list_results OR lists > regarding the order ID. If data from list_results.total exists, and is > referencing lists.id, which in turn is referencing orders.id through > lists.order_id, return o.max-lr.total. If data from list_results or > lists DOESN'T exist, I would just want to go ahead and return > orders.max. I was hoping the coalesce would be able to do this, but it > doesn't. The subquery is in there because frankly I'm not sure how to > do multiple left joins, which I think would have to exist. The easy way > out for me here I think would be to make list_results.order_id and leave > lists out of it, but then I'd have redundant data in two tables. Any > suggestions on this one? Multiple left outer joins? FROM LEFT OUTER JOIN USING () LEFT OUTER JOIN USING () WHERE ... In your case, SELECT (o.max - coalesce(sum(lr.total), 0)) FROM orders LEFT OUTER JOIN lists ON (orders.id = lists.order_id) LEFT OUTER JOIN list_results ON (lists.id = list_results.l_id) GROUP BY o.max But I'm not sure if that accomplishes what you're looking for or not. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] how to determine array size
On Mon, 2003-06-09 at 16:23, Forest Wilkinson wrote: > I need to enumerate the constraints on any given column in a table, so > I'm examining pg_constraint to get the relevant information. The > conkey array contains a list of constrained columns, and although I am > able to check conkey[1] for constraints on a single column, I would > like to properly handle multi-column constraints. You may have an easier time dealing with pg_get_constraintdef() than trying to get the info from the source. The above function outputs the necessary SQL to rebuild the constraint, and is used by pg_dump. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] how to determine array size
> Is pg_get_constraintdef() documented somewhere? I'd like to know it's > arguments, return format, and whether it will be supported in future > postgres releases. Support for it will improve, and it'll be around for a few releases anyway. > >From what I see in pg_dump.c, it appears to accept an oid from the > pg_constraint table, and only work with foreign key constraints. > True? That might be useful in some special-case code, but I really > want a method that will work with all types of constraint. (The idea The other types of constraints have been added in 7.4. In fact, it's the only way to fetch an accurate CHECK constraint in 7.4. Miscellaneous functions are documented at: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-misc.html -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] comparing querys
Take a look at EXPLAIN and EXPLAIN ANALYSE: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-explain.html On Wed, 2003-06-18 at 16:23, Lucas Lain wrote: > how can i compare two querys' eficiency??? > > TIA, -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] convert_numeric_to_scalar: unsupported type 354210
> select id from data where dec < 2.0; > ERROR: convert_numeric_to_scalar: unsupported type 354210 Domains exposed (and introduced) a number of interesting issues in regards to type switching for these things. Cast the 2.0 value to the domain: CAST(2.0 AS physreal) or quote it and let the system figure it out: dec < '2.0' Simply put, without quotes the parser assumes that 2.0 is a numeric, which doesn't have a direct < operator for use with the domain. Another alternative would be to create a new < operator, but thats more work than it's worth. This has been fixed for 7.4 (the system implicitly coerces the domain to numeric for the index comparison). -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Temporary table performance?
> I could then do some queries against the temporary table without having to > regenerate the results every time I want to show a "Page 299 of 500" toolbar. Performance wise temp tables are the same as a regular table but without WAL on the table contents. > Would I be better off just sucking this data into an in-memory data structure, > or can I use a temp table as an easy-to-use alternative? You are probably better off using a cursor. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Change the behaviour of the SERIAL "Type"
> Well, why not just use the Sequence? > Is there really such a performance hit when calling a trigger? > In Oracle, one usually does such a thing, as there is no such nice > workaround > as SERIAL. > Hmm, I am still thinking about a special kinf of SERIAL, maybe called > TRIGGERED_SERIAL which creates a trigger instead of a DEFAULT. DB2, Firebird, MSSQL? and some others have what they call GENERATOR support (IDENTITIES fall into this)-- which also happens to be in the SQL 200N proposals. Main Features (per proposed spec): - Not strictly integers (any expression on any datatype) - Optionally overridable or not -- which is what you're looking for - Attribute of the column. Not a datatype. This is an alternative for DEFAULT. I'm hoping to add IDENTITIES / GENERATOR support along these lines in 7.5, but I've not looked at those other databases to see how close their implementation matches spec -- whether it will make us compatible with them or not. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] ERROR: ExecEvalExpr: unknown expression type 108
> datetest=# select * from (select (select count(1) from (select von from
> daten union select bis as von from daten) as d1 where d1.von < d2.von)
> as number, von from (select von from daten union select bis as von from
> daten) d2) as table1 join (select (select count(1) + 1 from (select von
> from daten union select bis as von from daten) as d1 where d1.von <
> d2.von) as number, von from (select von from daten union select bis as
> von from daten) d2) as table2 using (number);
> ERROR: ExecEvalExpr: unknown expression type 108
>
> Is that a bug?
Yes. Regardless of whether this is a valid SQL statement, it shouldn't
be caught in this manner.
SubSelects have undergone a large simplification by Tom while
implementing read-only plans in the executor. This seems to have
accidentally fixed this case.
rbt=# create table daten
rbt-# ( id serial not null primary key
rbt(# , menge integer
rbt(# , von timestamp(0) without time zone
rbt(# , bis timestamp(0) without time zone
rbt(# );
NOTICE: CREATE TABLE will create implicit sequence 'daten_id_seq' for
SERIAL column 'daten.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'daten_pkey' for table 'daten'
CREATE TABLE
rbt=#
rbt=# INSERT INTO daten
rbt-# VALUES (DEFAULT, 2, '2000-01-01 10:00:00', '2000-01-01
12:00:00');
INSERT 17132 1
rbt=#
rbt=#
rbt=# INSERT INTO daten
rbt-# VALUES (DEFAULT, 3, '2000-01-01 11:00:00', '2000-01-01
14:00:00');
INSERT 17133 1
rbt=#
rbt=#
rbt=# INSERT INTO daten
rbt-# VALUES (DEFAULT, 1, '2000-01-01 14:00:00', '2000-01-01
15:00:00');
INSERT 17134 1
rbt=#
rbt=# INSERT INTO daten
rbt-# VALUES (DEFAULT, 8, '2000-01-01 12:00:00', '2000-01-01
16:00:00');
INSERT 17135 1
rbt=#
rbt=#
rbt=# INSERT INTO daten
rbt-# VALUES (DEFAULT, 4, '2000-01-01 10:00:00', '2000-01-01
11:00:00');
INSERT 17136 1
rbt=#
rbt=# select * from (select (select count(1) from (select von from
rbt(# daten union select bis as von from daten) as d1 where d1.von <
d2.von)
rbt(# as number, von from (select von from daten union select bis as von
from
rbt(# daten) d2) as bar join (select (select count(1)+1 from (select von
from
rbt(# daten union select bis as von from daten) as d1 where d1.von <
d2.von)
rbt(# as number, von from (select von from daten union select bis as von
from
rbt(# daten) d2) as foo using (number);
number | von | von
+-+-
1 | 2000-01-01 11:00:00 | 2000-01-01 10:00:00
2 | 2000-01-01 12:00:00 | 2000-01-01 11:00:00
3 | 2000-01-01 14:00:00 | 2000-01-01 12:00:00
4 | 2000-01-01 15:00:00 | 2000-01-01 14:00:00
5 | 2000-01-01 16:00:00 | 2000-01-01 15:00:00
(5 rows)
rbt=# select version();
version
PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4
(1 row)
--
Rod Taylor <[EMAIL PROTECTED]>
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] cleaning up useless pl/pgsql functions
On Mon, 2003-06-30 at 21:13, Tomasz Myrta wrote: > Hi > Do you have any easy script to remove all pl/pgsql function? After a lot of > changes inside "create or replace function..." scripts I have a big mess. I > want to remove all user defined pl/pgsql functions and restore some of them > from my scripts again. DROP PROCEDURAL LANGUAGE plpgsql CASCADE; CREATE PROCEDURAL LANGUAGE plpgsql ... -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Need help creating a BEFORE DELETE trigger
> The problem I'm looking at is: could this cause a recursion problem, where the > cascading deletion will try to cause the whole thing to cascade again? How It will only be able to delete the row (and cascade) once per row. The second time it tries to find the row, the row won't exist anymore. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] CREATE SEQUENCE fails in plpgsql function
On Tue, 2003-07-01 at 13:33, Tom Lane wrote: > Erik Erkelens <[EMAIL PROTECTED]> writes: > > DECLARE > > new_max_records ALIAS FOR $1; > > BEGIN > > CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE; > > > ERROR: parser: parse error at or near "$1" at character 39 > > You'll need to use EXECUTE to construct and execute that CREATE > SEQUENCE. Utility statements generally don't accept runtime parameters, > which is what the plpgsql variable looks like to the main parser. > > > Also, if there is a better mechanism to implement > > this, I'm all ears... > > There's an ALTER SEQUENCE command in CVS tip, though I'm not sure > I trust it in concurrent-usage scenarios :-( It shouldn't be trusted anymore than setval() should be. That is, changes take place immediately. Seems to me you might be better off just creating a 'count' table. Update the single row when it changes. By dropping / recreating the sequence you've already blocked concurrent transactions. The single row would have less to vacuum, where the sequence has quite a bit more. Another alternative is to use setval() on the sequence BUT first pull a FOR UPDATE lock on some blocking row (for concurrency reasons). SELECT * FROM pg_class WHERE relname = 'sequence name' FOR UPDATE; SELECT setval(); This would work equally well with ALTER SEQUENCE in 7.4. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] SEQUENCE and PRIMARY KEY
On Wed, 2003-07-02 at 11:30, Ralf Werny wrote: > Hi, > many clients like webmin and openoffice makes an > INSERT NULL if i give no value for a field because it is a sequence. > Is there a better way to solve this problem as using a trigger ? A trigger (maybe a rule) is the way to go about this. The alternative is to teach the bad clients to use DEFAULT rather than NULL when they expect the GENERATOR to create the value for them. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] SEQUENCE and PRIMARY KEY
> >A trigger (maybe a rule) is the way to go about this. > > > >The alternative is to teach the bad clients to use DEFAULT rather than > >NULL when they expect the GENERATOR to create the value for them. > > > Hmm, Rod, there really seems a demand for the GENERATOR feature :-) > Do you see any performance problems with a trigger instead of a default? > I cant imagine that its so much of a difference (ok, depending on the > trigger, a > lookup in the catalog is needed, but still it should be fast enough, right?) A trigger written in C will have similar speeds as processing the default. Plpgsql isn't that slow, but it is slower -- probably not noticeably for this operation. signature.asc Description: This is a digitally signed message part
Re: [SQL] max length of sql select statement ?
> Could be a solution?! > The question is - how long could the IN be? I'm not sure about IN specifically, but I know you can do: SELECT * FROM table WHERE col = '<1GB long file>'; It tends not to be friendly for Ram though :) signature.asc Description: This is a digitally signed message part
Re: [SQL] max length of sql select statement ?
> Maybe we can disuss that problem here again?! What exactly means > "max_expr_depth"? Thanks for any help If I'm not mistaken, max_expr_depth is used to catch runaway recursion (view a is select * from b, view b is select * from a). It's a tunable in postgresql.conf. Toss a couple of 0's behind the number and uncomment the line. signature.asc Description: This is a digitally signed message part
Re: [SQL] max length of sql select statement ?
On Wed, 2003-07-09 at 15:51, markus brosch wrote: > On Wed, 2003-07-09 at 17:45, Rod Taylor wrote: > > > Nobody a better idea? Why is the join of a temporary table (the IN > > > paramters) and the original table so slow? Any tricks here? > > > > Did you index and ANALYZE the temporary table? > > No! > > I have to do this "strange" and "long" statement also a view thousand > times ... so I always have to create the temp table again and again and > if I index the table it costs me more time. So.. which costs more. Building the index + fast query or doing the slow query? If you have anything more than a thousand lines in the temp table, I bet indexing it would be the better way to go. > As far I am relativly new to SQL, I ask you: > Where's the difference between and indexed temp (!!!) table and an > unindexed one? The main table is of course fully indexed ;-) One has an index, which means that other (much faster) methods may be available for the database to use. signature.asc Description: This is a digitally signed message part
Re: [SQL] Table Partitioning and Rules
On Thu, 2003-07-17 at 19:03, Josh Berkus wrote: > Girish, > > > > Essentially Im trying to store a persons information in a table in the > > > database. Since we could have millions of people, with duplicates! Ive > > > decided we need to partition the table into segments where all people with > > > the LastName starting from A to G will be in one table. H-N will be in > > > another table and O-Z in the third. Ive created a VIEW that does a UNION > on > > > all the tables. > > This sounds hideously inefficient and a management headache besides. I think > PostgreSQL will accept up to 2 billion rows in any one table, and splitting > stuff into 3 tables will not improve your performance ... quite the opposite. PostgreSQL will go well beyond 2 billion rows in a table. It just becomes difficult to use OIDs. signature.asc Description: This is a digitally signed message part
Re: [SQL] Why their is a limit in Postgresql (psql) Parameters..?
> The reason why it's not bigger is that there hasn't been enough people saying > "I need more parameters". That and a general speed penalty to all users of all functions. Make (whatever) the limitation is affect only those using a large number of parameters and you will find the limit set to a fairly high number. signature.asc Description: This is a digitally signed message part
Re: [SQL] silly NULL question
> Would return the row. Now with an upgrade to PostgreSQL 7.3 (yes, I know > there are many changes and we're working through them right now) the same > query returns nothing. Dropping the "AND parent_id = NULL" returns the row > as expected. NULL is similar to UNKNOWN. So, NULL = NULL is the similar to UNKNOWN = UNKNOWN. Since you don't know it, how can you tell if they're equal or not? Syntax you're looking for is: AND parent_id IS NULL If you really really really need = NULL (due to some MS product which ignores SQL standards -- say MS Access) there is a toggle in the postgresql.conf file to allow automated conversion of = NULL to IS NULL within the server. signature.asc Description: This is a digitally signed message part
Re: [SQL] function returning setof performance question
> The performance hit is tiny, we're talking less than 1/2 a second, > but when I've done this sort of thing in Oracle I've seen a performance > increase, not a decrease. Thats just plain strange (never tried on Oracle). Why in the world would adding the overhead of a function call (with no other changes) increase performance? The function has additional overhead in the form of the plpgsql interpreter. You may find a c function will give close to identical performance as with the standard view so long as the query is the same. One thing to keep in mind is that the view can be rearranged to give a better query overall. The exact work completed for the view may be different when called from within a different SQL statement. Most functions -- some SQL language based functions are strange this way -- cannot do this signature.asc Description: This is a digitally signed message part
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
I think you can do some constraints in 7.2, but 7.3 will allow dropping them all in that fashion. On Wed, 2003-07-30 at 11:52, Elielson Fontanezi wrote: > Hi all! > > Who can tell me what postgres version supports ALTER TABLE... DROP > CONSTRAINT without > the need of droping the table to remove a simple coinstraint. (link) > >>\\\!/< 55 11 5080 9283 >!_"""_! Elielson Fontanezi >(O) (o) PRODAM - Technical > Support Analyst > ---oOOO--(_)--OOOo--- > Success usually comes to those who are too busy to be looking for > it. > 0 0 > ---()--( > ) > \ () / > \_/\_/ > > > signature.asc Description: This is a digitally signed message part
Re: [SQL] Does a the block of code within a stored procedure constitute
> What I need to know is whether or not this is multi-user safe, i.e., will > the block of code in the procedure execute as a transaction so that if > more than one clerk creates an expense report for the same employee > simultaneously is it possible or impossible that value of the > employee.expense_report_seq gets updated by the second clerk between the > SELECT and UPDATE statements invoked by the first clerk? > > And as a follow-up, should I add the FOR UPDATE clause to the SELECT > statement? SELECT .. FOR UPDATE would be appropriate. However, you could also do an update + 1 first and the select second to find what you changed the value to. signature.asc Description: This is a digitally signed message part
Re: [SQL] join optimization problem
How about this? It should spit out the same number, but be quite a bit quicker at it. Untested of course... select t.thread_id, t.forum_id, t.thread_title, t.thread_owner, t.thread_owner_id, t.date_created, t.thread_hits, t.poem_reference, t.bArchived, count, maxdate from fbof_thread t LEFT OUTER JOIN (select thread_id , count(msg_id) as count , max(date_created) as maxdate from msg group by thread_id ) as tab ON m.thread_id = t.thread_id where t.forum_id = 1 and t.bArchived = 0 and t.bSticky = 0 order byt.date_created desc > can anyone point out to me where I'm going wrong here? I can't seem to make > it faster for the life of me I've tried adding indices on all the main > fields etc but nada. I'm not subscribed the list currently so please reply > to my address as well as the list. signature.asc Description: This is a digitally signed message part
Re: [SQL] select query that would join two databases
Take a look at the dblink() contrib module. PostgreSQL does not currently support inter-database joins BUT it does offer Schemas which is often enough. Take each of your source databases and toss them into two different schemas of the same database. On Wed, 2003-07-30 at 07:28, Keith H.K. Lam (U1 Tech.) wrote: > dear all, > > How to create a query that would join two databases? In MSSQL, i use > > select * from dbA.dbo.tableA a dbB.dbo.tableB b where a.id=b.id > > Is there anyway to write the above query in postgressql ? > > Please email me back. > Thanks, > Keith > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > signature.asc Description: This is a digitally signed message part
Re: [SQL] What day is it - when it isn't NOW()?
'2003-08-04' is ambiguous. It could be a text string, a date, something
else. So cast it.
SELECT to_char('2003-08-04'::date, 'Day');
> SELECT to_char(now(), 'Day'); returns Friday as I'd hoped.
> But how do I return the day of the week for a specific date other
> than now()? I'm looking for something along the lines of:
> SELECT to_char('2003-08-04', 'Day') without much success.
>
> Can anyone point me to the right function/combination of functions
> to achieve this?
>
> Thanks!
>
> ---
> Thomas Good e-mail: [EMAIL PROTECTED]
> Programmer/Analyst phone: (+1) 718.818.5528
> Residential Services fax: (+1) 718.818.5056
> Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
>
> // Welches ist das groessere Verbrechen?
> // Massenvernichtungswaffen besitzen oder sie erfinden?
>
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
signature.asc
Description: This is a digitally signed message part
Re: [SQL] join optimization problem
> thanks for that - I tried it out, fixed the minor typos and it ran at almost > the same speed! Any other ideas? Could you post an explain analyze for both queries? There is always an application change. Add a trigger onto msg that will bump the date and count on msg_sum when a msg is added. This will support virtually unlimited msg table growth (any aggregate will eventually be slow on a large table). Just be sure to vacuum the msg_sum table regularly (once per 25% turnover) -- possibly as often as once a minute! signature.asc Description: This is a digitally signed message part
Re: [SQL] length of recordset read through a cursor
Yes, a move takes less time, but can still a significant amount of time. Do you need to know exactly what to expect? Run ANALYZE recently? A cheat I've used before is to parse the EXPLAIN (not EXPLAIN ANALYZE) output for the expected number of records involved. If that number was less than 2000, I MOVE through them for an exact count -- otherwise display as approx . In most cases it's within 50% of actuality, sometimes better, but very few people care. They just want to know whether the information from their search is within the next screen or two. On Tue, 2003-08-05 at 07:13, Knut P. Lehre wrote: > >> After declaring a cursor, one way of obtaining the length of the > >resultset > >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a > >"MOVE nn" > >> where nn is the length of the resultset. (A negative MOVE can then be > >used > >> to allow starting to fetch records from the beginning of the > >resultset.) > >> > >> Is there another, possibly faster way? > >> > >Looks like you're using libpq (because you mention PQcmdStatus), > >then after declaring a cursor and FETCH ALL, try > > > >1.3.4. Retrieving SELECT Result Information > > > >PQntuples Returns the number of tuples (rows) in the query result. > > > >int PQntuples(const PGresult *res); > > > >I'm not exactly sure what you're trying to achieve or going to do, > >so if I misunderstood you, ask again. > > > >Regards, Christoph > > Thanks for your reply. > What I'm trying to do is the following: I want to browse through a view > containing more than 1 records. To avoid slowing things down too much, > I would like my client program to receive (through the network) only the > records that are to be displayed on the screen. I believe I could do this > by declaring a cursor and then fetching the parts of the resultset I need. > It would be useful to know the size of the resultset immediately after the > cursor has been declared. How do I get this information? I could of course > fetch all of the resultset, but that is what I am trying to avoid. > Shouldn't it be quicker to perform a move through the set than fetching it? > I found that moving zero records results in a move to the end of the > resultset, with a command status returning the number of records moved. > Although I expected this method to take less time than a fetch (does it?), > I was wondering if there might be another way to get the size of the > resultset that can be fetched through the declared cursor. > > KP > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > signature.asc Description: This is a digitally signed message part
Re: [SQL] Insert a description while creating a table
On Wed, 2003-08-13 at 07:56, [EMAIL PROTECTED] wrote: > Hi, > > I want to insert descriptions at the columns of my tables but without > using the command COMMENT ON. I want to do it together with the table > creation. Is that possible? > > I wanna do something like this: > > create table test ( > id serial 'Descripitions about ID', > name varchar(50) 'Descriptions about NAME' > ); Probably not going to happen in the backend. However, you should be able to accomplish that with a little bit of Perl to pre-process the SQL. signature.asc Description: This is a digitally signed message part
Re: [SQL] Porting from PL/SQL to PLPGSQL
> we are facing a few problems with PL/SQL Code.. > > 1)In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I > can't find a replacement for that in > PostGreSQL. What does it do? > 2 Oracle have a function USERENV to get the user session > information.Is there any replacement available in PostGres. ? CURRENT_USER (I believe this is per spec) > 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE > for triggers in PostGreSQL. You might have some luck with Rules (CREATE RULE) if you are writing data to a different table. Otherwise you're stuck with somewhat more complicated triggers. signature.asc Description: This is a digitally signed message part
Re: [SQL] Do it exist?
> In another language I would do: > find first Order where Order.Order_num = "". > if avaialble Order then it_exists = true. > > In PostgreSQL ?: > SELECT DISTINCT Order_num from Order_header where Order_num = ''; > > Is this close? If so, how to raise flage that row exists? > > > How we do somthing like this in SQL? Since you don't care about a value, just pull TRUE and use LIMIT to restrict to a single entry (much faster than DISTINCT, but not as portable). SELECT TRUE FROM Order_header where Order_num = '' LIMIT 1; Do a count of the number of rows returned. 1 row means it exists, 0 rows means it does not exist. It seems to me like you intend to insert a row with that number shortly after? You should be aware that this process will introduce a race condition (may not exist during test, but does exist shortly after during insert). If Order_num is Distinct on that table (or another), just try the insert. If it fails, increment the number and try again. If you don't mind gaps in the numbers, then a sequence will fare much better (easier and faster). signature.asc Description: This is a digitally signed message part
Re: [SQL] Reverse pattern match.
On Mon, 2003-08-18 at 03:05, Moonstruck wrote:
> I want to create a table of regular expression patterns (for assessing
> phone numbers), something like:
> CREATE TABLE CallType ( pattern varchar primary key,
> typevarchar,
> rateint4);
> INSERT INTO CallType VALUES ('0[3-9]','Interstate Call',50);
> INSERT INTO CallType VALUES ('9___','Local Call',25);
> INSERT INTO CallType VALUES ('0011__%','International Call',100);
>
> Then determine call types, based on a match, something like:
>
>
> PhoneNumber := '99116633';
> SELECT type, rate FROM CallType where pattern LIKE PhoneNumber;
SELECT type, rate FROM CallType WHERE PhoneNumber ~ pattern;
signature.asc
Description: This is a digitally signed message part
Re: [SQL] "SELECT IN" Still Broken in 7.4b
> Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > be changed at the same time, because it really is unusable for anything > over a couple of thousand values. Changed to do what? I suppose that the ability to combine several index scans via a bitmap would help to linearize those, but that is far from an IN(valuelist) specific enhancement. signature.asc Description: This is a digitally signed message part
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Ensure your IN list is unique. You might find better times by through an indexed temp table. On Wed, 2003-08-20 at 16:32, Mike Winter wrote: > I'm sure many on this list are sick of hearing about this problem, but it > was on the fix list for 7.4, but doesn't appear to have been changed. > > You can see one of the many threads on the problem at: > http://archives.postgresql.org/pgsql-sql/2003-05/msg00352.php > > Basically, queries of the form SELECT FROM WHERE IN > () take forever for high numbers of rows in the IN clause. > We've done timing on 7.3 and 7.4b and there is no speed improvement on > these queries. > > Does anyone know what the status of this bug is? signature.asc Description: This is a digitally signed message part
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 2003-08-20 at 17:41, Stephan Szabo wrote: > On Wed, 20 Aug 2003, Rod Taylor wrote: > > > > Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > > > be changed at the same time, because it really is unusable for anything > > > over a couple of thousand values. > > > > Changed to do what? > > One possibility might be to act as if the valuelist was a table and do the > IN as if it were that way, rather than treating it as a set of ORs. That > would be basically like doing the temporary table solution, but without > requiring the user to do it. Is the temp table version any faster? I realize it has a higher limit to the number of items you can have in the list. signature.asc Description: This is a digitally signed message part
Re: [SQL] "SELECT IN" Still Broken in 7.4b
> What is measured by the \timing option? The figures reported > are slightly larger than those loged when the log_duration parameter > is true. The time of the psql client. It will include round trip activity including network overhead. signature.asc Description: This is a digitally signed message part
Re: [SQL] MINUS & ROWNUM in PostGres
> 1)Do we have a replacement in PostGres for MINUS operator of Oracle . I believe MINUS is non-standard word for EXCEPT, correct? > 2 Also I need to find an alternative for ROWNUM in oracle.. If you are looking for a unique identifier, try using the OID. signature.asc Description: This is a digitally signed message part
Re: [SQL] Porting from Oracl to Postgres
On Fri, 2003-09-05 at 00:24, Jomon Skariah wrote: > > Hi, > > Thanks for your replys. > > We are facing another problem now. > > we need to find an alternative for Oracle's ADD_MONTHS in PostGres.. Guessing based on the name that it adds a quantity of months to a timestamp. How about an SQL Interval? now() + interval '15 months' List of functions: http://www.postgresql.org/docs/7.3/interactive/functions.html signature.asc Description: This is a digitally signed message part
Re: [SQL] SQL subqueries newbie help
> I'd like to write a query which returns following information regarding > each item: item, date of very first event, very last event. > Is this possible? I think I can write several SELECT queries and > procces them by an application or possibly write some procedure, but > what is better solution? Something like the below should do it: SELECT item , min_date , min_event , max_date , max_event FROM items JOIN (SELECT min(date) AS min_date , event AS min_event , item FROM events GROUP BY item) AS mn USING (item) JOIN (SELECT max(date) AS max_date , event AS max_event , item FROM events GROUP BY item) AS mx USING (item); signature.asc Description: This is a digitally signed message part
Re: [SQL] Why table has drop, but the foreign key still there?
> insert into state (state_code,state) values ('GU','Guam');
> drop table whitepage;
> delete from state where state_code = 'GU';
> ERROR: Relation "whitepage" does not exist
Old version of PostgreSQL? Effort went into cleaning up inter-object
dependencies in 7.3. I don't recall having that particular issue in
versions prior either.
That said, you can remove the triggers that are on table "state"
beginning with the characters "RI". psql -d state will tell you what
they are.
signature.asc
Description: This is a digitally signed message part
Re: [SQL] SQL subqueries newbie help
On Sun, 2003-09-07 at 07:42, Alexei Chetroi wrote: > On Sat, Sep 06, 2003 at 01:21:36PM -0400, Rod Taylor wrote: > > > I'd like to write a query which returns following information regarding > > > each item: item, date of very first event, very last event. > > > Is this possible? I think I can write several SELECT queries and > > > procces them by an application or possibly write some procedure, but > > > what is better solution? > > > > Something like the below should do it: > > > > SELECT item > > , min_date > > , min_event > > , max_date > > , max_event > > FROM items > > JOIN (SELECT min(date) AS min_date > > , event AS min_event > > , item > > FROM events > > GROUP BY item) AS mn USING (item) > [skip] > > Thanks everybody for responses. I'm trying this one, but psql complains > on queries like "SELECT min(date), event FROM events GROUP BY item" that > events must be GROUPed or used in an aggregate function. Why this > happens and why it needs be so? Oh yeah, sorry. It needs to be like that because otherwise it is unsure what value to use for event. What you really want is a min function that runs a min on date, and returns the appropriate event -- which doesn't exist, but I believe could be created (see docs on Creating an Aggregate function if interested) In the mean time, find the event based on the dates and item id. This does assume that an item may only have one event per date. SELECT item , min_date , (SELECT event FROM events WHERE date = min_date AND item = items.item) , max_date , (SELECT event FROM events WHERE date = max_date AND item = items.item) FROM items JOIN (SELECT min(date) AS min_date , max(date) AS max_date , item FROM events GROUP BY item) AS ev USING (item) signature.asc Description: This is a digitally signed message part
Re: [SQL] MINUS & ROWNUM in PostGres
On Mon, 2003-09-08 at 09:44, Jomon Skariah wrote: > Hi, > > Do we have any replacement for REPLACE() of Oracle in PostGres? What does replace() do? String replacement? http://www.postgresql.org/docs/7.3/interactive/functions-string.html replace(string text, from text, to text) signature.asc Description: This is a digitally signed message part
Re: [SQL] MINUS & ROWNUM in PostGres
> The problem is we are using PostGres 7.1.In this version REPLACE() is not > available. It sounded like you were just starting to work on the change over. I highly suggest upgrading to 7.3 at the very least, 7.4 if you're going to be a few months prior to going to production. A ton of good work has been put into PostgreSQL in the last couple of years. Anyway, you'll need to write your own replace() if you stick with that release. signature.asc Description: This is a digitally signed message part
Re: [SQL] Q: select query
> in other words, all but the first row of a group. Interesting question. The below should work and be quick so long as there is a UNIQUE(col1, col2) constraint. SELECT col1 , col2 FROM j WHERE col2 != (SELECT col2 FROM j AS jsub WHERE col1 = j.col1 ORDER BY col2 ASC LIMIT 1); signature.asc Description: This is a digitally signed message part
Re: [SQL] A simple way to Create type ...?
> I guess, ideally it'd be > create type AddressType AS varchar(50) ; > but it does not work. Only one keyword off. SQL calls this a domain. They're limited in 7.3, but much improved (not yet perfect) for 7.4. http://www.postgresql.org/docs/7.3/interactive/sql-createdomain.html signature.asc Description: This is a digitally signed message part
Re: [SQL] Creating Index
>-> Seq Scan on table1 (cost=0.00..20.00 rows=1000 width=0) (actual Run VACUUM ANALYZE, then repost your EXPLAIN ANALYZE results please. signature.asc Description: This is a digitally signed message part
Re: [SQL] [PERFORM] sql performance and cache
> > Perhaps you are confusing it with the MySQL query cache? > Is there plan on developing one (query cache)? For the most part, prepared queries and cursors give you a greater advantage due to their versatility -- both of which we do have. In the cases where an actual cache is useful, the client application could do it just as easily or temp tables can be used. I suspect it would be implemented more as a caching proxy than as an actual part of PostgreSQL, should someone really want this feature. signature.asc Description: This is a digitally signed message part
Re: [SQL] How can I produce the following desired result?
On Tue, 2003-10-14 at 22:09, aicean wrote: > How can I produce the following desired result? I'm not sure I understand the problem, but you might want to try a subselect in the FROM. SELECT FROM table JOIN (SELECT goodid FROM table WHERE ) AS tab USING (goodid) WHERE signature.asc Description: This is a digitally signed message part
Re: [SQL] see a current query
On Wed, 2003-10-22 at 05:26, sad wrote: > Hello > > i'am logged in as superuser (pgsql) > trying to > SELECT * FROM pg_stat_activity; > and seeing NULLs instead of current_query column& You need to change the stats settings in postgresql.conf. By default the current query is not enabled as it does cause a performance loss. signature.asc Description: This is a digitally signed message part
Re: [SQL] naming conventions constraint
rbt=# create table bob_is(your_uncle integer, constraint "bob_is#your_uncle" check(true)); CREATE TABLE rbt=# \d bob_is Table "public.bob_is" Column | Type | Modifiers +-+--- your_uncle | integer | Check constraints: "bob_is#your_uncle" CHECK true You need to quote strings which contain special characters. On Thu, 2003-10-23 at 16:30, Julian North wrote: > I'm at the very beginning of working with postgres. > > The aim is to replace / build a number of very large and very complex > database currently residing in Oracle, MSSQL and Informix. > > In order to coordinate across a medium size DBA team I instituted a standard > constraint naming convention suitable for the above 3 platforms. > > For example a foreign key would be FK_table#reftable#column. > > Unfortunately I don't appear to be able to use it with postgres. > > This is because I can't use the # character in constraint names. > > Does anybody have any suggestions as to an alternative that is also a single > char? (I'm limited to 30 chars cos of oracle and manyof the current names at > the limit). > > Any help appreciated, > > > > Julian. > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > signature.asc Description: This is a digitally signed message part
Re: [SQL] Table versions
> What I did next, is put a trigger on pg_attribute that should, in theory, > on insert and update, fire up a function that will increment a version System tables do not use the same process for row insertion / updates as the rest of the system. You're trigger will rarely be fired. signature.asc Description: This is a digitally signed message part
Re: [SQL] A tricky sql-query...
On Sun, 2003-11-02 at 19:42, Mark Stosberg wrote: > On 2003-10-22, Timo <[EMAIL PROTECTED]> wrote: > > > > You can't have any recursion in an pure sql-query, can you? > > It depends on how you think of recursion, I'd say. You join on the same > table a number of times, by giving it a different alias each time. You > have to manually specify (or generate with application code) all these > aliases and joins, though. Sometimes people use this technique to > implement tree structures in SQL. Not to mention the WITH .. RECURSIVE clause, not yet in PostgreSQL. signature.asc Description: This is a digitally signed message part
Re: [SQL] DateDiff in PostgreSQL
> ie, a function that returns difference of two dates(timestamp) in days
> or months or year..
>
> The - operator for timestamp retuns the intervel in days only.
rbt=# select extract('days' from current_timestamp -
'2003-01-01'::timestamp);
date_part
---
310
(1 row)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] transaction processing after error in statement
> be recovered either. When committing a transaction the effects of all > operations that did not fail will be made permanent. This is how > transaction processing is described in the literature. I would be interested in reading that (URLs please) as I didn't see anything in the spec that was interesting on this topic. 4.8.5 from Framework (part 01) An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data. The "execution result is completely successful" could certainly be used to back up PostgreSQLs choice to force a rollback. However, it doesn't differentiate between execution of what the user requested, and execution of recovery procedures on the successful user elements. Irregardless, I wish a commit on a failed transaction would throw an error -- END is good enough for Rollback or Commit. For PostgreSQL to implement this we need Savepoints or nested transactions internally since in many cases data is physically written in order to perform things like Foreign Key constraint checks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] transaction processing after error in statement
> Although i am not aware of the roots of this discussion but would like > to > comment at this point . > > When we work with sequences an aborted transaction does have > a permanent effect on the last value of sequence. Is this behaviour > not a violation of above defination of transaction ? I believe you are correct, which is probably why Oracle offers serialized sequences for those who want them to be. Sequences have been explicitly documented as functioning the way they do as a concession for performance. ---(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
Re: [SQL] Multicolum index and primary key
> Suppose by example that one have a table1 with a primary key over three > field (a, b, c): .. > are the indexes over (a) and (a, b) redundant (and so useless)? Yes, they are redundant not not necessarily useless. In short, an index with 3 keys will be larger than an index with 1 key, as such PostgreSQL may choose to use the single key index to reduce the number of pages it needs to pull off the disk. That said, if the 3 key index is hit regularly, it is likely to be in memory where the rarely hit single key index is not. This would make going through the 3 key data faster (although there is more of it) than retrieving the single key data from disk, then processing. To top it all off, managing 3 indexes takes significantly longer during INSERT and UPDATE than manging a single larger index does. So... Are they useless? The primary key is required, so it's index is required. Do a majority of the queries against that table only supply one or two pieces of information? If so, you may benefit, as these indexes will tend to be in memory. Is access on the table mostly read? Is the write penalty worth the increased speed of write? Is the additional storage space worth it? Indexes on thousands or million of tuples are not free. 3 indexes will probably consume as much diskspace as the original table did thus doubling your storage requirements. Finally, if everything is useful, I suggest you re-order some of the indexes. a, ab, abc all require a to be a part of the query. There is 0 benefit if b or c are supplied without a. If you have determined 3 indexes will be useful, you might try a, ba, cba. This way if b or c are supplied without a, they will receive some benefit of the index with negligible impact to the queries that do use a. NOTE: I have made an assumption that the distribution of a, b and c are equivalent. You will want the more selective field first in your index to reduce the number of disk accesses -- so couple that with the odds that b or c will be supplied without a. ---(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] rules and return values question
> tables but it introduces too much overhead. In any case, we need the return > value (or an exception) from the function but there does not seem a way to > return it. Can't you use erreport() to return an exception message? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Sometimes referential integrity seems not to work
> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > for foreign keys. So I guess Enio is getting but ignoring the error In 7.4 truncate is transaction safe. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] column alias and group by/having/order
> select val1+val2 as val > from some_table > group by val having val>1; > ERROR: Attribute "val" not found > > Is it a bug or a feature? It's a mis-feature that group by accepts aliases of the select list. Having is proper. ---(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
Re: [SQL] 7.4 - FK constraint performance
> In this precise example, could you not:
> 1. Check index for value
> 2. If found, seq-scan
>
> Of course that's only going to be a sensible thing to do if you're expecting
> one of two results:
> 1. Value not there
> 2. Lengthy seq-scan if it is there
Most of the queries are going to be for the other values (in which case
you've wasted an index scan) which is minor, but in the event there is a
single 239 you're still taking a big hit.
That is an awful lot of work to handle the non-existant case only.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] 7.4 - FK constraint performance
On Thu, 2004-02-12 at 23:25, Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > Statistics say there are 10 values. Statistics list the 10 most common
> > values (all of them). Given this, would it not be reasonable to assume
> > that 239 is a recent addition (if there at all) to the table and not
> > very common?
>
> We don't know that it's 239 when we make the plan. In order to know
> that, we'd have to abandon caching of RI check query plans and re-plan
> for each row. That strikes me as inevitably a losing proposition.
Right, sorry, I forgot this was out of a fk trigger.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] 7.4 - FK constraint performance
On Thu, 2004-02-12 at 20:10, Tom Lane wrote:
> ow <[EMAIL PROTECTED]> writes:
> > Sounds pretty bad for my case. Any way to avoid the 10% scan?
>
> Can't see how we optimize your case without pessimizing more-common cases.
> Sorry.
Statistics say there are 10 values. Statistics list the 10 most common
values (all of them). Given this, would it not be reasonable to assume
that 239 is a recent addition (if there at all) to the table and not
very common?
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
On Thu, 2004-02-12 at 05:06, Fredrik Wendt wrote: > Hi! > > I read posts telling me that NULL values are considered greater than > non-null values. Fine. Is there a way to explicitly reverse this? ORDER BY column IS NOT NULL, column ASC; ---(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] Inserting NULL into Integer column
> and then you can foreach across the input:
>
> foreach($fields as $f){
> if (!$_POST[$f]){
> $_POST[$f]='DEFAULT';
> } else {
> $_POST[$f] = "'".$_POST[$f]."'";
> }
> }
Default in quotes isn't going to work, and please tell me you escape
those things with pg_escape_string() at some point.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inserting NULL into Integer column
> Note that the ' marks aren't part of the string, they are the delimiter of > the string, and I always run every server with magic_quotes_gpc on. > > anything else? :-) Good point. I looked at the single quotes of the second line and somehow the DEFAULT got quoted as well ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Compiling pl/pgsql functions
> AFAIK there's not much you can do for obfuscation of pl functions right
> now since someone will be able to see the src text in pg_proc. However,
> are you allowing people that you don't want to see the code access to
> write arbitrary sql to the database?
This is another one of those items where it would be nice if users
didn't need access to read the system tables, but instead could rely on
the information schema (with extensions) to see what they own or have
access to use -- but nothing else.
Sometimes HR gets paranoid about billing seeing their business logic, or
lack thereof, but accounting needs to use both sets of information to do
their work.
Otherwise, having each group relegated to their own schema with
semi-public views is a nice way to pass information from department to
department for small companies. Sure beats the spreadsheets on the
central filer approach.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] Newbie Query question
> However, this query does not give me the result I expected. It appears that > the database engine first calculates the cartesian product of the tables A > and B and then evaluates the query. Hence, I get multiple matches for Yup.. WHERE filters the results of the join. > Is there any way to do this, other than using UNION?? This is what you want. I think you could come out with what you want in other ways, but this is by far the most appropriate. ---(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
Re: [SQL] partial unique constraint
On Tue, 2004-04-06 at 10:29, Robert Treat wrote:
> Trying to come up with the proper syntax to meet the following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
Tt takes 2 steps.
CREATE TABLE ...
CREATE UNIQUE INDEX ... (bar) WHERE baz = true;
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
signature.asc
Description: This is a digitally signed message part
Re: [SQL] SQL challenge--top 10 for each key value?
On Thu, 2004-04-08 at 19:33, Greg Stark wrote: > Jeff Boes <[EMAIL PROTECTED]> writes: > > > I headed off in the direction of groups of SELECTs and UNIONs, and quit when I > > got to something like four levels of "SELECT ... AS FOO" ... > > four? wimp, that's nothing! > > ok, seriously I think there's no way to do this directly with straight SQL. > You would have to define a non-immutable function that has some temporary > storage where it keeps track of how many it has seen. I don't believe that is true, though it is certainly is in PostgreSQL. The spec has the ability to apply a progressive aggregate on the results of a query (window function). Meaning you can accomplish things like counting (ROW_NUMBER) or running totals. Something along the lines of the below would accomplish what you want according to spec. ROW_NUMBER() is a spec defined function. (6.10 of SQL200N) SELECT * FROM (SELECT ROW_NUMBER() OVER (DISTINCT query) AS counter ) WHERE counter > 10; ---(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] SQL challenge--top 10 for each key value?
On Fri, 2004-04-09 at 18:43, Greg Stark wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > > Rod, > > > > > Something along the lines of the below would accomplish what you want > > > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of > > > SQL200N) > > > > Great leaping little gods! They added something called "row number" to the > > spec? > > > > Boy howdy, folks were right ... the ANSI committee really has completly blown > > off the relational model completely. > > If it's like Oracle's rownum then it's the row number of the *output*, not the > position on disk. So it's not entirely blowing off the relational model any > more than ORDER BY does. > > The weird thing is the number of cases where you want ORDER BY or rownum > inside subselects. Which the solution to the original question needed. It's not really like Oracles row num at all, though I suppose you can emulate rownum using it. The intention is that you will use it for "aggregates" like running totals, moving averages, counting, etc. http://www.devx.com/getHelpOn/10MinuteSolution/16573/1954?pf=true ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can someone tell me why this statement is failing?
> Can anyone tell me why this SQL statement is not > matching with the row from the table below? I think > I'm going mad! Indeed. The row is NOT in that range. 1082377320 is > 1082375100 not <= it ---(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] Server Side C programming Environment Set up
On Thu, 2004-04-22 at 01:18, Peter Eisentraut wrote: > Kemin Zhou wrote: > > IN chapter 33 Extending SQL > > 33.7.5 Writing Code > > when run pg_config --includedir-server > > I got /usr/local/pgsql/include/server but my machine does have this > > directory > > make install-all-headers > > It's explained in the installation instructions. That doesn't happen on most platforms in the standard package. Are you proposing that packagers create a postgresql-headers package and depend on that? If this is the suggested way of solving the headers portion of the problem then lets tell the packagers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Server Side C programming Environment Set up
On Thu, 2004-04-22 at 10:11, Peter Eisentraut wrote: > Am Donnerstag, 22. April 2004 15:58 schrieb Rod Taylor: > > > make install-all-headers > > > > > > It's explained in the installation instructions. > > > > That doesn't happen on most platforms in the standard package. > > It certainly happens in all the packages that have ever come by me (maybe > after a little complaining). Okay, I'll start submitting patches for the packages we tend to use here. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem with slow select
On Wed, 2004-04-21 at 11:00, francescosaf wrote: > hi > > I have two tables: Please send results of EXPLAIN ANALYZE for the query in question. Thanks ---(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] Check a value in array
On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote: > Hi all. > > I have to check if a value is in an array. > > I've got a date array in a table and I would like to perform queries > like: > > SELECT * FROM table WHERE date IN dates_array; If you're using 7.4 or later, try: SELECT * FROM table WHERE date = ANY(dates_array); This will work without the contrib package. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Procedure failing after upgrade
On Tue, 2004-05-04 at 09:32, patkins wrote: > All, > > I just upgraded to the latest version from 7.2.x and now a procedure is failing. > > Please tell me what I'm doing wrong! Please include the actual error message produced. That said, I'm getting an interesting error. It appears as if the integer array type is being confused for a boolean. WHILE id_array[count_it] LOOP It is expecting WHILE LOOP. Make the id_array[count_it] expression into a boolean rather than an integer. Something like: WHILE id_array[count_it] IS NOT NULL LOOP ---(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
Re: [SQL] most efficient way to manage ordering
> Ideally, I'd like to figure out a single SQL query that can be run
> afterwards to clean up the dsply_order to make sure that each number occurs
> only one time and that there are no gaps.
Well... by far the easiest way to approach this is not to clean up the
gaps. Removing gaps will only make things pretty, not easier or faster.
This is one of the many times it is best to differentiate between what
is displayed and what is used for functional purposes.
CREATE TEMPORARY SEQUENCE images_display_count;
SELECT nextval('images_display_count') AS display_order
, *
FROM images
WHERE galleryid = 1
ORDER BY real_order;
DROP SEQUENCE images_display_count;
There are ways of replacing the sequence that may be faster, but this
will address your concern. Do your updates, etc. via real_order and show
the user display_order.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Last insert id
On Tue, 2004-06-15 at 03:05, Andrei Bintintan wrote: > "Is it safe to use "select max(table1_id) from table1" after the insert?" > > Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT). No, this is not safe outside of the serializable isolation. rbt=# begin; BEGIN rbt=# select max(id) from l; max - 1 (1 row) rbt=# insert into l values (2); INSERT 62597 1 rbt=# select max(id) from l; max - 3 (1 row) rbt=# commit; COMMIT I inserted 3 from another connection after 2 was inserted. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Queries across multiple database
On Wed, 2004-06-30 at 18:54, Garth Thompson wrote: > I am in the process of migrating the databases for an application from mysql > to postgres. The application is uses several different databases. From > what I have read, postgres still does not support queries across multiple > databases. If this is still true, does anyone have any tricks to get around > this? I'm sure this is a common problem for people migrating from mysql. > The obvious answer is to just migrate everything into one larger postgres > database, but I would like to avoid this if possible - many a line of could > would need to be touched if this was done. In this case, a PostgreSQL Schema is essentially the same as a MySQL Database. Use one database, and multiple schemas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Constraint->function dependency and dump in 7.3
On Mon, 2004-07-12 at 08:29, SZŰCS Gábor wrote: > Dear Fellow Countymen, > > I fear none of your answers are acceptable for me. Also, from Csaba's > answer, this sounds to me more like a bug in pg_dump v7.3. It is a bug (or a missing feature) that should be solved with v7.5 for restoring to 7.5 or later database. Until then, you'll need to re-order the dump by hand (splitting data from schema can be useful). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Constraint->function dependency and dump in 7.3
> Checked, and So do you say, this problem persists in dbs dumped from 7.4 to > 7.4 too? i.e. upgrading to 7.4 (which we tested for quite some time now) > won't help? There may have been some minor fiddling to make it easier, but I wouldn't call it fixed by any means. > trying dump confirmed this :( Even tried adding a line to pg_depend but > didn't seem to change anything. The 7.5 version of pg_dump will be the first one to use pg_depend to attempt to get the object order correct. I would hold off on an upgrade until October (or so) and goto 7.5 instead. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Constraint->function dependency and dump in 7.3
On Tue, 2004-07-13 at 13:42, SZŰCS Gábor wrote: > Dear Rod, > > Thanks. It'll be a pain to have two versions between the prod and devel > servers, but I'll forward this info to the chief. You can make this part easier on yourself. Dump the structure from production and migrate it to devel (fix the dump file). Keep this file. >From now on when applying changes to production, keep the structural changes applied as a separate SQL file (numbers work well). When building a new box to duplicate production: 1. Apply all patches in order for i in `ls *.sql` ; do cat $i | psql test_db ; done 2. Do a data dump of production and restore that to the testing area pg_dump --data-only prod_db | psql test_db Now you don't need to worry about whether the production schema will or will not dump properly, since you can reproduce that using external files. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query plan discrepancies
Have you run ANALYZE recently?
Please send back EXPLAIN ANALYZE for the below query.
> EXPLAIN from DB 2 (doesn't come back):
>QUERY PLAN
> -
> HashAggregate (cost=7763.55..7763.56 rows=1 width=8)
>-> Nested Loop (cost=4363.86..7763.55 rows=1 width=8)
> -> HashAggregate (cost=4363.86..4363.86 rows=200 width=146)
>-> Seq Scan on word_stats_base (cost=0.00..4126.09
> rows=95109 width=146)
> -> Index Scan using medline_abstract_tokens_norm on
> medline_abstract_tokens (cost=0.00..16.99 rows=1 width=8)
>Index Cond: ((medline_abstract_tokens.norm)::text =
> ("outer".norm_token)::text)
>Filter: (pmid = 7968456)
> (7 rows)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] date_format in postresql
On Tue, 2004-07-20 at 20:55, azah azah wrote: > Hi, > I want convert from mysql to postresql, > in mysql, query to database using the code as below: > > date_format(submittime, "%W %M %e, %Y - %r") to_char(submittime, 'format string') http://www.postgresql.org/docs/7.4/static/functions-formatting.html ---(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
Re: [SQL] date_format in postresql
On Tue, 2004-07-20 at 23:53, azah azah wrote: > Thanks Chris and Rod. > > I think I have a problem because not many function existing in > postresql that i installed. > How can i get all the build-in functions because the basic function > to_char is not existing in the database? Which version of PostgreSQL are you using? select version(); > On Tue, 20 Jul 2004 22:39:38 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote: > > On Tue, 2004-07-20 at 20:55, azah azah wrote: > > > Hi, > > > I want convert from mysql to postresql, > > > in mysql, query to database using the code as below: > > > > > > date_format(submittime, "%W %M %e, %Y - %r") > > > > to_char(submittime, 'format string') > > > > http://www.postgresql.org/docs/7.4/static/functions-formatting.html > > > > > > ---(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 > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
