Re: [SQL] Assigning a timestamp without timezone to a timestamp

that a timezone name shouldn't contain digits ... which is bogus, but we'll have to think carefully about how to improve it ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send

Re: [SQL] Foreign key reference counting strategy?

Michael Fuhr [EMAIL PROTECTED] writes: On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote: No, I don't think so, because the DELETE will already be holding exclusive lock on the doomed PK row, which any would-be inserters of matching FK rows will be blocked on. AFAICS the DELETE should

Re: [SQL] Foreign key reference counting strategy?

integrity trigger, maybe it would work... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] could not connect to server

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

Re: [SQL] Is this a bug? Deleting a column deletes the constraint.

tables. Zero-column tables are not in themselves very useful, but disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec restriction. regards, tom lane ---(end of broadcast

Re: [SQL] Is this a bug? Deleting a column deletes the constraint.

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

Re: [SQL] PG 8.2beta reordering working for this case?

with a very slow clock-reading capability. Each node output row counted by explain analyze takes two gettimeofday() calls, and apparently it's not unusual for those to take several microseconds on cheap motherboards, even when the CPU is nominally very fast. regards, tom lane

Re: [SQL] PG 8.2beta reordering working for this case?

will be covered by the 8.2 improvements. There isn't any understanding of how to commute joins and unions though ... (offhand I'm not even sure of the conditions under which such a thing would be safe). regards, tom lane ---(end of broadcast

Re: [SQL] SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join

select FOR UPDATE OF a in this situation, it's just the B side that is problematic. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Too much time to delete 19000 rows

to have an index on the referencing side, but you pay for it when you update or delete in the master table... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] i have table

have changed. (To name only the most obvious problem...) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

the old results to apply justify_hours() to the subtraction result for themselves. Not sure what the fallout would be, though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [SQL] age() vs. timestamp substraction

some differences in the calculation compared to a plain timestamp subtraction. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Postgresql quey planner

of cosmetic code rearrangements and added a regression test for the problem. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your

Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

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

Re: [SQL] Time interval sums

there are appropriate operators at least as far back as 7.3. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] LOG: logger shutting down

? A restorecon on the postgres executables and everything under /var/lib/pgsql might help. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Finding context for error log

Kyle Bateman [EMAIL PROTECTED] writes: Is there an easy way to get postgres to spit out the SQL statement it was parsing when it generated the error? log_min_error_statement = error is what you're looking for. regards, tom lane ---(end

Re: [SQL] ERROR: could not write block 196261 of temporary file: No space left

that you see UPDATE 0 right after that is further evidence for this theory --- after the failure, there's nothing in fragment_table, so the UPDATE just falls through.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you

Re: [SQL] Nested loops are killing throughput

= dps.packet_uuid) Total runtime: 18205.880 ms If packet_status is large, that seems like a perfectly reasonable plan to me. If not ... what data type is packet_uuid? Is its equality op marked mergeable or hashable? regards, tom lane ---(end of broadcast

Re: [SQL] Nested loops are killing throughput

the equality function is marked volatile, and so the planner is afraid to try to use it for merging or hashing. (It's also not marked strict, which means you can trivially crash the backend by passing it a null ...) regards, tom lane ---(end of broadcast

Re: [SQL] Aggregates with internal state type?

to copy it. The same goes for other pseudotypes. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [SQL] Aggregates with internal state type?

to have a length word at the front. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Sorting items in aggregate function

will probably destroy the ordering by b.code. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] case insensitive regex clause with some latin1 characters fails

). It has to be one that expects LATIN1 encoding. The current regex code is generally not able to deal with locale-specific behaviors in UTF8 encoding, but it should work for single-byte encodings as long as you've got the locale setting right. regards, tom lane

Re: [SQL] text+number, find largest entry

-report example in the SELECT reference page if you have no idea what I'm talking about.) this lookup does not have to be especially fast. Good ;-) ... otherwise changing your schema would definitely be indicated. regards, tom lane ---(end of broadcast

Re: [SQL] Problem with FOR UPDATE

/SHARE clause not found in FROM clause Use the alias, ie, DD. Remember that an alias hides the real name of that table for all purposes in the current query. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched

Re: [SQL] Evaluation of if conditions

down to the core SQL engine. So it fails on OLD.bar not being defined, long before the expression evaluator gets to think about whether TG_OP = 'INSERT' or not. So, yeah, you want to rewrite it as two separate IF-tests. regards, tom lane ---(end

Re: [SQL] Postgres regexp matching failure?

Mario Splivalo [EMAIL PROTECTED] writes: Now, here is what happens if I try this in postgres: pulitzer2=# select '+mario' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$'; I'm thinking you've forgotten to double your backslashes. regards, tom lane

Re: [SQL] pg_dump

(or turn it off temporarily) and redo initdb. You can find more info if you search our archives for selinux. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Postgres regexp matching failure?

, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] help with pagila

in the future, but that's where it stands today. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Error on function parameters

boolean Well, is it insertPoints or inserirPontos? Is schema base in your search path? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] The length of the sql query

to run it on. The 1Gb upper limit for MySQL is a pretty academic number too, for exactly the same reasons. Have you tried putting a 1Gb query string into MySQL? regards, tom lane ---(end of broadcast)--- TIP 1: if posting

Re: [SQL] double precision vs. numeric

is in the SQL standard, it's certainly not going anywhere. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's

Re: RES: [SQL] Lock Problem

the database often enough? Is this table suffering from bloat? That's my bet. With numbers like those, that table has to get vacuumed every few minutes to keep performance from going into the tank. regards, tom lane ---(end of broadcast

Re: [SQL] SQL92 compliance

. Even if we were willing to do that, I think we'd also have to give up using bison to generate the parser :-( because some constructs would require more than one-token lookahead. regards, tom lane ---(end of broadcast)--- TIP 5

Re: [SQL] All columns from table in a joined query

schedule where sessionnumber = 165 order by (select min(meetingday.date) from meetingday where schedule.id = meetingday.scheduleid); regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Deleting Functions

with regprocedure: select 'drop function ' || p.oid::regprocedure || ';' from pg_proc p join pg_namespace b on (p.pronamespace=b.oid) where nspname='public'; [ tries it ... ] ... except that there's no regprocedure-to-text cast. How annoying :-( regards, tom lane

Re: [SQL] Using bitmap index scans-more efficient

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

Re: [SQL] Using bitmap index scans-more efficient

Kyle Bateman [EMAIL PROTECTED] writes: Tom Lane wrote: Before 8.2 the optimizer has no ability to rearrange the order of outer joins. Do you have time to try your test case against CVS HEAD? OK, I figured it out--grabbed the latest snapshot (hope that is what you need). My results

Re: [SQL] Using bitmap index scans-more efficient

and p.left 2345 and p.right 2345; so that you can constrain the range of left values scanned. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] PL/pgSQL and PHP 5

work. One possibility is that you created the function in a schema that isn't part of the application's search path. Other than that, look for *really* silly errors, like not creating the function in the same database the application is connected to ... regards, tom lane

Re: [SQL] autoupdating mtime column

--- do you have a real use-case for suppressing mtime updates? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Error: out of memory in create table as

. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [SQL] alter column type from boolean to char with default

Andrew Hammond [EMAIL PROTECTED] writes: Alternatively, you already have the USING clause to tell you how to alter the data. How about using it to alter the default as well? The reasons not to do that are already set forth in the ALTER TABLE man page. regards, tom lane

Re: [SQL] alter column type from boolean to char with default doesn't work

, but with the change the DROP part would be re-ordered to occur first. So maybe the cure is worse than the disease. OTOH that's a pretty silly example, whereas wanting to ALTER TYPE and fix the default in a single command is quite reasonable. Thoughts? regards, tom lane

Re: [SQL] alter column type from boolean to char with default

Rod Taylor [EMAIL PROTECTED] writes: On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote: Hmm ... the way I would have expected to work is alter table posts alter column deleted drop default, alter column deleted type char(1) using (case when deleted then 't' else 'f' end), alter column

Re: [SQL] PostgreSQL server terminated by signal 11

value being bogus. I'm pretty well convinced now that we're looking at a problem with corrupted data. Can you do a SELECT * FROM (or COPY FROM) the table without error? regards, tom lane ---(end of broadcast)--- TIP 2: Don't

Re: [SQL] PostgreSQL server terminated by signal 11

region is then just after the last ctid you see. You can look at those blocks with pg_filedump -i -f and see if anything pops out. Check the PG archives for previous discussions of dealing with corrupted data. regards, tom lane ---(end of broadcast

Re: [SQL] PostgreSQL server terminated by signal 11

, provoke the crash, and do bt --- search for gdb in the archives if you need details). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Disk is full, what's cool to get rid of?

not gonna be able to afford when your back is to the wall.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] PostgreSQL server terminated by signal 11

of arguments shown for slot_attisnull suggests that all we're going to get is a list of function names, without line numbers or argument values. If that's not enough to figure out the problem, can you rebuild with --enable-debug to get a more useful stack trace? regards, tom lane

Re: [SQL] PostgreSQL server terminated by signal 11

remains to be seen ... we need that stack trace! regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] DBD::Pg ... how would I format this prepare?

Marc G. Fournier [EMAIL PROTECTED] writes: if I do: NOW() + '? day'::interval it, of course, takes the ? as a literal ... so is there some way I can do this such that I can do the placeholder? NOW() + n * '1 day'::interval n can be any numeric value ... regards, tom

Re: [SQL] Error when trying to use a FOR loop

plpgsql; You've got a small error in the layout: the DECLARE part goes before BEGIN not after. Swap the first two lines of the function. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [SQL] System catalog table privileges

don't want the users messing with, and not get too worried about whether they know the tables exist or not. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Help with privilages please

of (at least) select privilege to PUBLIC. You'll need to revoke that if you don't want every user to have that privilege implicitly. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking

to the tmp_created checkpoint, but I don't think this will pick up the new rows in the real table, and the INSERT will fail again. Why do you think that? If you're running in READ COMMITTED mode then each statement takes a new snapshot. regards, tom lane

Re: [SQL] Use of cmin in psql

sathiya moorthy [EMAIL PROTECTED] writes: what is the use of the cmin system column http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze

Re: [SQL] How to pass array of values to a stored procedure

(arrayvalue) syntax is what you want. Note however that this can't be turned into an indexscan on field1 in existing releases (8.2 will be able to do it). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9

Re: [SQL] Regular Expression in SQL

into non-capturing parentheses. Until this is fixed, your best bet is to use the POSIX-regexp form of substring(). You can't sneak non-capturing parens through similar_escape, because it'll try to escape the ? ... regards, tom lane ---(end

Re: [SQL] Can function results be used in WHERE?

break anything of interest. This would certainly not be so if we were to randomly replace integer constants in general WHERE conditions with non-constant values. regards, tom lane ---(end of broadcast)--- TIP 3: Have you

Re: [SQL] Local variable and column name conflict

, so probably the best answer is don't do that. I think it's entirely too error-prone anyway ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe

Re: [SQL] Can't drop table

help you figure out which one. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Alternative to Select in table check constraint

. The unique-index hack that Michael suggested amounts to hand-optimizing the sub-SELECT constraint into something that's efficiently checkable. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet

Re: [SQL] CASE is not a variable

Keith Worthington [EMAIL PROTECTED] writes: The following is a section of code inside an SQL function. SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause (there can be only one). regards, tom lane ---(end of broadcast

Re: [SQL] avg(interval)

do you arrive at that conclusion? I haven't done the math, but by eyeball an average of four-something days doesn't look out of line for those values. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase

Re: [SQL] permissions from system catalogs

://www.postgresql.org/docs/8.1/static/functions-info.html regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: Fwd: [SQL] Start up question about triggers

. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [SQL] unknown sqlstate code

. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [SQL] Repetitive code

. PG doesn't (currently) have direct support for materialized views, but it has some tools you can get the effect with. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] UTF-8 Problem ?

present in 7.3), and even then it didn't have anything to do with support for multibyte encodings like UTF8. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http

Re: [SQL] listen_addresses = '*' ok, specific address(es) no

FATAL: could not create any TCP/IP sockets There should be more info than that --- AFAICS all the failure paths in that code emit LOG messages. Perhaps you have log_min_messages set too high to allow the info to come out? regards, tom lane

Re: [SQL] listen_addresses = '*' ok, specific address(es) no

pg_hba.conf. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!)

your system so it's all on the same page about whether IPv6 is supported. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your

Re: [SQL] Prepared statements in PGSQL functions

time trying to outsmart the language. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Requirement for PostgreSQL Database Developer

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

Re: [SQL] Error with limit clause

to the backend ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through

Re: [SQL] Good examples of calling slony stored procedures

where the right people to ask hang out. Don't have the address at hand. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your

Re: [SQL] COPY to table with array columns (Longish)

, this will perform a lot better than an array-based translation. And no, in neither case will you be able to import that file without massaging it first. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Problems Testing User-Defined Function

. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [SQL] datestyle on windows environment: how to set?

values in http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] SELECT DISTINCT too slow

: SELECT DISTINCT Key FROM MRTPContactValue Try SELECT Key FROM MRTPContactValue GROUP BY Key The select distinct code is a bit old and crufty, GROUP BY is usually smarter. regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] Am I crazy or is this SQL not possible

ON (email_broadcast_id) * FROM email_broadcast_history ORDER BY email_broadcast_id, date_sent DESC You order by the DISTINCT ON fields, then one or more additional fields to select the representative row you want within each DISTINCT ON group. regards, tom lane

Re: [SQL] Am I crazy or is this SQL not possible

Aaron Bono [EMAIL PROTECTED] writes: Is this SQL-99 compliant or a PostgreSQL specific query? I really like it and have never seen this before. DISTINCT ON is a Postgres-ism, I'm afraid. It's pretty handy though. regards, tom lane ---(end

Re: [SQL] timestamp query doesn't use an index ...

. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Recursive SELECT problem

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

Re: [SQL] Find min and max values across two columns?

the lines of case when xy then x else y end regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Help with a seq scan on multi-million row table

(do you know the true figure?). A larger statistics target would probably produce a better number and hence a better join estimate. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [SQL] Help with a seq scan on multi-million row table

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

Re: [SQL] Help with a seq scan on multi-million row table

for user_url_tag.user_url_id contain? Have you analyzed that table recently? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] ERROR: plan should not reference subplan's variable

Catalin Pitis [EMAIL PROTECTED] writes: ERROR: plan should not reference subplan's variable Do you have any clue why does this happen? It's a bug :-(. Thanks for the test case --- I'll look into it tonight or tomorrow, if no one beats me to it. regards, tom lane

Re: [SQL] ERROR: plan should not reference subplan's variable

Catalin Pitis [EMAIL PROTECTED] writes: ERROR: plan should not reference subplan's variable I've applied a patch for this; will be in 8.1.4. http://archives.postgresql.org/pgsql-committers/2006-05/msg00016.php regards, tom lane ---(end

Re: [SQL] Outer joins?

should be in form: obj_id1 o1att1_value o1att2_value o1att3_value obj_id2 o2att1_value o2att2_value o2att3_value ... This isn't an outer-join problem, it's a crosstab problem. Try the crosstab functions in contrib/tablefunc. regards, tom lane

Re: [SQL] Slightly confused error message

query, but coverage_area is being used outside an aggregate without having been grouped by. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail

Re: [SQL] Slightly confused error message

to the specific variable occurrence that it's complaining about. That would help at least somewhat in cases like this. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] INSERTing values from usertypes how-to?

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

Re: [SQL] Migrating a Database to a new tablespace

-aR would give more information than guessing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Migrating a Database to a new tablespace

Markus Schaber [EMAIL PROTECTED] writes: Tom Lane wrote: Instead of assuming anything, why don't you look in the tablespace directory and see what's there? A quick ls -aR would give more information than guessing. There's plenty of stuff there, 8.8 Gigabytes in total. The question is how

<    2   3   4   5   6   7   8   9   10   11   >