Re: [SQL] Multi ordered select and indexing

1 int4_reverse_order_cmp(int4, int4); Now you can just use ASC/DESC in your ORDER BY ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Permissions not working

suspect that you got it wrong somehow ... I dont want user 'test' to access any tables from the 'ups' database, i tried revoking permissions it still doesnt work. What did you revoke? What does psql's \z command show for the problem tables? regards, tom lane

Re: [SQL] Which SQL command creates ExclusiveLock?

ExclusiveLock *on a table*. The pg_locks row you show represents ExclusiveLock on a transaction number. Every transaction gets ExclusiveLock on its transaction number for the duration of its existence. regards, tom lane ---(end of broadcast

Re: [SQL] Server Side C programming Environment Set up

off. It seems silly to abandon the not-trivial work you and other people have already put into the contrib build system; and also silly to expect gborg projects to individually adapt it to their needs. regards, tom lane ---(end of broadcast

Re: [SQL] Server Side C programming Environment Set up

, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Join issue on a maximum value

the discussion of SELECT DISTINCT ON in the SELECT reference page; the weather report example may be illuminating. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble

, but no modern database has such a restriction anymore ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Server Side C programming Environment Set up

Peter Eisentraut [EMAIL PROTECTED] writes: Am Donnerstag, 22. April 2004 07:59 schrieb Tom Lane: For instance I've been meaning to ask what to do about this open bug report: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=112244 Well, perhaps getting the tutorial to compile should

Re: [SQL] Server Side C programming Environment Set up

have an idea what would be needed. (Note this would also provide a usable solution to the build-the-tutorial problem I mentioned.) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [SQL] lifetime of temp schema versus compiled image of plpgsql proc

case? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] lifetime of temp schema versus compiled image of plpgsql proc

*schema* and not a temp table. There's something very strange here, because the temp schema name for a given session is definitely fixed for the life of the session. regards, tom lane ---(end of broadcast)--- TIP 6: Have you

Re: [SQL] Is there an easy way to normalize-space with given string functions

, I don't know why we don't offer a built-in one --- the needed regex engine is in there anyway. I guess no one has gotten around to getting agreement on a syntax.) regards, tom lane ---(end of broadcast)--- TIP 7: don't

Re: [SQL] Prepared Statements and large where-id-in constant blocks?

no percentage in doing lots of work in the JDBC driver to prefer the IN form at the moment. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs

Re: [SQL] relation X does not exist

, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Update is very slow on a bigger table

on the table, or an update lock on one of the rows to be updated. The pg_locks view might help you determine who's the culprit. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map

Re: [SQL] trigger/for key help

suppose we could use long randomly-generated names like ewjncm343cnlen, but are those really easier to work with? I think a more useful approach is to treat it as a documentation problem. Perhaps an example in the ALTER TABLE man page would help. regards, tom lane

Re: [SQL] Formatting Functions and Group By

by date_trunc('month', myCol) order by to_char(date_trunc('month', myCol), 'MM ') regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes

Re: [SQL] trigger/for key help

unquoted IDs starting with $ would be. So that's a dead end. I think if we wanted to change the default assignment of constraint names we'd just go with ordinary identifiers that we hope won't conflict with names the user picks. regards, tom lane

Re: [SQL] function returning array

:= parseString(); regression'# tmpv := results[1]; regression'# RAISE NOTICE '' tmpv = % '',tmpv; regression'# return tmpv; regression'# end' language plpgsql; CREATE FUNCTION regression=# select foo(); NOTICE: tmpv = abc foo - abc (1 row) regards, tom lane

Re: [SQL] cursors and for loops?

Dennis [EMAIL PROTECTED] writes: I am wondering if I can use a cursor in a for loop. Something like LOOP FETCH ...; EXIT WHEN NOT found; ... END LOOP; should do it. regards, tom lane

Re: [SQL] trigger/for key help

it owns. You could probably remove the pg_depend entries to make it possible to drop the table. Use ALTER TABLE next time, eh? Hand manipulation of the system catalogs is *not* for those who don't know exactly what they are doing. regards, tom lane

Re: [SQL] SQL challenge--top 10 for each key value?

. regards, tom lane ---(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] Utility of recursive queries?

implementation be noticably more efficient that a straightforward implementation in plpgsql Most likely not ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http

Re: [SQL] notify and leaks?

, though, it will clean up before exiting. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] could not devise a query plan

=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] writes: If this is a bug and has been fixed since 7.4.1, I'd take the task to compile a newer version and see how it fares. It's still there in CVS tip :-(. Will look into it today. regards, tom lane

Re: [SQL] partial unique constraint

); create unique index fooi on foo (bar) where baz = true; Personally I'd spell that last as just where baz ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan

Re: [SQL] could not devise a query plan

combination of inputs, so we probably ought to do something about it. I have applied the attached patch to 7.4. (It would probably work in 7.3 too, but no guarantees.) regards, tom lane Index: costsize.c === RCS file

Re: [SQL] Function To Log Changes

;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] [pgsql-advocacy] SQL Spec Compliance Questions

seems closely related, but I do not understand the business about a self-referencing column. I have a feeling that it might be a mutant version of our notion of inheritance ... regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] Is it normal that functions are so much faster than inline queries

? regards, tom lane ---(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] SQL Spec Compliance Questions

that they need? In sufficient detail that we could actually answer? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL

Re: [SQL] date_part stored procs

. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Invalid Unicode Character Sequence found

, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] autocommit

Kemin Zhou [EMAIL PROTECTED] writes: Could we add one simple switch to psql (the client front end or the library) --noautocommit? psql already has this, see \set AUTOCOMMIT. regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] Sorting an aggregated column

not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] move forward 0 from foo;

do something about that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Sorting an aggregated column

to determine the order of inputs to a user-defined aggregate function. See for instance http://archives.postgresql.org/pgsql-general/2003-02/msg00917.php regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists

Re: [SQL] inverse of day of year

; regards, tom lane ---(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] triggers on commit

at the same time. I believe deferred AFTER triggers are fired just before commit. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] psql: FATAL 1: IDENT authentication failed for user error - Urgent pls

to get the postmaster to notice changes in its config files. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [SQL] Date format issue

. It looks like UPDATE pg_proc SET prorettype = 1114 WHERE oid = 1026; would fix it, but I counsel testing that in a scratch database ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe

Re: [SQL] Can statement_timeout emulated NOWAIT?

the same error code as query cancel, which is pretty bogus also.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] working with unix timestamp

'::timestamptz); date_part 1079459165 (1 row) Note that I have been careful to work with timestamp with time zone (timestamptz) here. If you work with timestamp without time zone, your results will be off by your GMT offset. regards, tom lane

Re: [SQL] Line length in pl/pgsql function

in a construct of: FOR result IN SELECT ... LOOP Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but I don't know of any problems since then. Could you submit a *complete* test case, rather than making us guess the details? regards, tom lane

Re: [SQL] About pg_dump

as before. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] randomized order in select?

a random entry efficiently using an index. Dig around in the mail list archives for details. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining

Re: [SQL] currval() without specifying the sequence name

fragile, not less so. 3) If one sequence is used, in many cases it'll have to be of type int8 and pgSql does not handle searches with int8 very nicely. Quote or cast and you're fine. But what does that have to do with knowing a sequence name? regards, tom lane

Re: [SQL] Functional index and string concatenation

directly: create index str_idx on strtable( (str1 || str2) ); The disadvantage of the textcat() locution is that the planner will only match it up to queries that also say textcat(). regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] ANALYZE error

tries to update pg_statistic second fails with the above error. It's moderately annoying, but not dangerous. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command

Re: [SQL] query optimization

the tables are tiny, which hardly seems likely given that you're complaining about the speed. If it's still bad after you VACUUM ANALYZE, send EXPLAIN ANALYZE (not just EXPLAIN) output and maybe we can give some help. regards, tom lane ---(end of broadcast

Re: [SQL] debugging query to put message in pg logfile?

not to think of this because psql strips -- comments before sending commands. But I believe all the lower-level libraries will pass them through. (If you need to pass loggable comments through psql, I think the /* ... */ form will work.) regards, tom lane

Re: [SQL] calling function

is considered int4 (or int8 or numeric if large enough), and there's no automatic downcast to int2. You could write 2::int2 or some such, but on the whole I'd recommend declaring the function to take int4 not int2. regards, tom lane ---(end

Re: [SQL] returning a recordset from PLpg/SQL

details_for_profile(...) as x(doc_id int4, doc_title varchar(256), ...); regards, tom lane ---(end of broadcast)--- TIP 9: the planner

Re: [SQL] What's wrong with my date/interval arithmetic?

-with-time-zone, whereas your other values are evidently timestamp without time zone. You did not say what timezone setting you are using, but I think the discrepancy is probably explained by that. regards, tom lane ---(end of broadcast

Re: [SQL] returning a recordset from PLpg/SQL

Terence Kearns [EMAIL PROTECTED] writes: I tried RETURNS SETOF RECORD but that doesn't work Sure it does, if you use it correctly. Better show us what you did. regards, tom lane ---(end of broadcast)--- TIP 2: you can

Re: [SQL] Convert INT to INTERVAL?

; ?column? -- 4 years (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Scalar in a range (but textual not numeric)

= (SELECT pr_min FROM table WHERE pr_min = 'ABCDE' ORDER BY pr_min DESC LIMIT 1) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire

Re: [SQL] Postgres DB

. That doesn't mean that such a DB is guaranteed to exist, or even that anything is going to try to create it for you. It's just a default behavior that people have found handy. regards, tom lane ---(end of broadcast)--- TIP 6

Re: [SQL] postgresql multiple insert slow

be the INSERT command that chokes - is there a better way to do it ? You probably want to add BEGIN/COMMIT operations around the loop. See the documentation's tips on bulk data loading: http://www.postgresql.org/docs/7.4/static/populate.html regards, tom lane

Re: [SQL] Function

by following up an existing unrelated thread. Start a new thread with an appropriate subject line. Otherwise you're wasting the time of the other people on the mailing list, who are exactly the people who might answer your question. regards, tom lane ---(end

Re: [SQL] create function atof?

('zit'); ERROR: invalid input syntax for type double precision: zit CONTEXT: PL/pgSQL function atof while casting return value to function's return type regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our

Re: [SQL] Creating constraint sometime fail in a transaction

or exactly what went wrong, it's impossible to say ... regards, tom lane ---(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] CHAR(n) always trims trailing spaces in 7.4

. regards, tom lane ---(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] Distributed Transactions

series. We don't make such releases just to amuse ourselves. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Indexes and statistics

. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

to it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

differently from other operators on character strings, so it doesn't seem like a very attractive option to me. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

--- zit (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Return relation table data in a single value CSV

. Here, myagg() will see its input ordered by increasing values of baz. Before 7.4 this method didn't work because the planner was too stupid to avoid re-sorting the subquery output. You could only make it work in cases where you weren't doing grouping ... regards, tom

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

concatenation is a text operator. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

behavior is more consistent than what we had before, but I'm willing to be persuaded to change it again if someone can give an alternate definition that's more workable than this one. regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

elein [EMAIL PROTECTED] writes: Apparently the ::char is cast to varchar and then text? No, directly to text, because the || operator is defined as taking text inputs. But there's no practical difference between text and varchar on this point. regards, tom lane

Re: [SQL] Indexes and statistics

the extra I/O that the planner is expecting? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Date format problems

, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] returning multiple resultset?

by returning opened cursors. See the plpgsql docs' discussion of working with cursors. Of course this will only work for resultsets that you can specify as a SQL query. regards, tom lane ---(end of broadcast)--- TIP 7: don't

Re: [SQL] Unable to convert date to tm

.* or older, I'd suggest an update. The particular issue here seems to be that 7.1 does not contain a workaround for broken mktime() library routines that reject dates before 1970. regards, tom lane ---(end of broadcast

Re: [SQL] Passing composite values to functions

of anyone working on it now though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] arrays and polygons

)), ...}' The array parser doesn't think parens are special, so it's not going to magically distinguish array commas from polygon commas for you. BTW, if you are using 7.4, the ARRAY[] constructor syntax might be easier to use. regards, tom lane ---(end

Re: [SQL] column alias and group by/having/order

be computed last. We have extended the SQL92 requirement (unadorned aliases in ORDER BY) to allow the same in GROUP BY, but we don't take it to the level of allowing them inside arbitrary expressions. regards, tom lane ---(end of broadcast

Re: [SQL] 7.4 - FK constraint performance

for this to allow EXISTS() subqueries to be planned properly; see the tuple_fraction stuff in planner.c. We just can't get at it via SPI ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet

Re: [SQL] 7.4 - FK constraint performance

Stephan Szabo [EMAIL PROTECTED] writes: On Fri, 13 Feb 2004, Tom Lane wrote: I was looking at that last night. It seems like we could add a LIMIT at least in some contexts. In the case at hand, we're just going to error out immediately if we find a matching row, and so there's no need

Re: [SQL] 7.4 - FK constraint performance

execute foo(239); When I try it I see an indexscan plan, but maybe there's some aspect of your setup that's causing problems. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives

Re: [SQL] 7.4 - FK constraint performance

as retrieving 10% of the table, and under that assumption it's quite right to use a seqscan. If this estimate is not right, perhaps you could give us a more accurate view of the column statistics? regards, tom lane ---(end of broadcast

Re: [SQL] 7.4 - FK constraint performance

ow [EMAIL PROTECTED] writes: --- Tom Lane [EMAIL PROTECTED] wrote: I think it must be using a seqscan for the foreign key check query. 2) prepare foo(my.dint) as SELECT 1 FROM ONLY my.large x WHERE small_id = $1 FOR UPDATE OF x; explain analyze execute foo(201); QUERY PLAN Seq Scan

Re: [SQL] 7.4 - FK constraint performance

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. regards, tom lane ---(end of broadcast)--- TIP 9

Re: [SQL] 7.4 - FK constraint performance

ow [EMAIL PROTECTED] writes: --- Tom Lane [EMAIL PROTECTED] wrote: Can't see how we optimize your case without pessimizing more-common cases. I think other RDBMSs simply use preset value instead of partial table scan when there's not enough stat info. Might be a better way. The problem here

Re: [SQL] Bug or Feature?

. DROP COLUMN foo shouldn't depend on whether there are other columns besides foo. In short, yes, it's a feature. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [SQL] Error in declaring %rowtype for recurrence

. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Implementation of a bag pattern using rules

NEW; You could also extend the trigger to handle the delete-upon-reaching-zero logic. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister

Re: [SQL] Index not used - now me

adjusting the cost settings based on only this example. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] TIME ZONE SQL

to me that your approach to the problem is all wrong, and you need to be using timestamp-based calculations not time-of-day-based calculations. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive

Re: [SQL] Novice SQL Question

. But that's not necessarily the way that will get the result you want. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere

Re: [SQL] update more than 1 table (mysql to postgres)

that hardly seem to deserve that description :-(). This thing has not been thought through. I'm sure the actual behavior of the corner cases in MySQL is just whatever happened to fall out of their implementation. regards, tom lane ---(end of broadcast

Re: [SQL] Sometimes referential integrity seems not to work

that had TRUNCATE TABLE would allow you to apply it despite the existence of foreign-key constraints on the table. Recent releases won't though. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free

Re: [SQL] Empty String Comparison Standard compliant?

that explains the exact example you cite of boolean comparison results. If it were taking the '' as a NULL then both comparisons ought to return NULL. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] How to retrieve N lines of a text field.

ago, and it is worth every penny if you do much of anything with regexes. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] query not using index for descending records?

. The index code needs comparator functions not to leak memory, and I doubt that that could be guaranteed with a SQL function. You'd probably have speed issues too. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe

Re: [SQL] query not using index for descending records?

out all that boilerplate ... regards, tom lane ---(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] Aggregate function error in 7.4

... regards, tom lane *** src/backend/parser/parse_agg.c.orig Sat Nov 29 14:51:51 2003 --- src/backend/parser/parse_agg.c Wed Jan 28 02:25:53 2004 *** *** 98,104 parseCheckAggregates(ParseState *pstate, Query *qry) { List *groupClauses

Re: [SQL] Question about isolation

the lack of SetQuerySnapshot inside functions is a bug; so the behavior might change in future.) Using SERIALIZABLE mode would probably make your code more future-proof, but if you are presently seeing failures, there's some other effect involved here. regards, tom lane

<    8   9   10   11   12   13   14   15   16   17   >