Re: [SQL] RETURNS SETOF primitive returns results in parentheses
Mario Splivalo <[EMAIL PROTECTED]> writes: > biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); > php_get_subfield_data_repeating1 > -- > (AnÄeli) > (ofsajd) > (2 rows) > I have return values in parentheses. You're getting bit by plpgsql's perhaps-excessive willingness to convert datatypes. Your returnValue variable is not a varchar, it is a record that happens to contain one varchar field. When you do "RETURN NEXT returnValue", plpgsql has to coerce that record value to varchar, and it does that by converting the record value to text ... which produces the parenthesized data format specified at http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604 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] why vacuum
"Bath, David" <[EMAIL PROTECTED]> writes:
> ... Note that Sybase/MS-SQL's
> check constraint model asserts the constraint BEFORE the trigger, which
> discourages you from attempting to check and handle meaning of data!
Er, doesn't PG do it that way too?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] why vacuum
Kenneth Gonsalves <[EMAIL PROTECTED]> writes: > i was in a minor flame war with a mysql guy - his major grouse was that > 'I wouldnt commit mission critical data to a database that needs to be > vacuumed once a week'. This guy is not worth arguing with. > So why does pg need vacuum? Every database needs maintenance operations. PG is designed in a way that exposes the maintenance operations to the control of the DBA a bit more than most other DBMSes do: specifically, you get to decide when some of the overhead work happens. We think this is a feature, because you can schedule the overhead for low-activity periods (nights, weekends, whatever). In other DBMSes the equivalent work happens as part of foreground queries, no matter how time-critical they might be. Now, there's no doubt that for a database run by a non-expert person who can't even spell DBA, exposing this sort of knob isn't very helpful. So there's work afoot to provide automatic maintenance tools (ie, autovacuum). Over time I think autovacuum will get smart enough that even experts will usually use it. But that point will only be reached when autovacuum has some idea about doing more work during low-load periods. Unless MySQL invents some concept equivalent to VACUUM, they won't have any prayer at all of being able to shift maintenance overhead to low-load times. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] broken join optimization? (8.0)
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote: >> in php (for example) it's frequently nice to get the structure of a >> table without any data, > Have you considered "SELECT * FROM mytable LIMIT 0"? Indeed. > I see the same behavior in the latest 8.1beta code. Maybe one of > the developers will comment on whether optimizing that is a simple > change, a difficult change, not worth changing because few people > find a use for it, or a behavior that can't be changed because of > something we're not considering. Not worth changing --- why should we expend cycles (even if it only takes a few, which isn't clear to me offhand) on every join query, to detect what's simply a brain-dead way of finding out table structure? I can't think of any realistic scenarios for a constant-false join clause. The relevant bit of code is in initsplan.c: /* * If the clause is variable-free, we force it to be evaluated at its * original syntactic level. Note that this should not happen for * top-level clauses, because query_planner() special-cases them. But it * will happen for variable-free JOIN/ON clauses. We don't have to be * real smart about such a case, we just have to be correct. */ if (bms_is_empty(relids)) relids = qualscope; Possibly you could get the planner to generate a gating Result node for such a case, the way it does for constant-false top level WHERE clauses, but I really doubt it's worth any extra cycles at all to make this happen. The proposed example is quite unconvincing ... why would anyone want to depend on the existence of a "dual" table rather than LIMIT 0? 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] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Because the rule converts those inserts into, effectively, >> >> INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); >> >> and there are no longer any matching OLD rows in the view. > Is this behaviour also present in Postgres 7? It's always been like that. We've jiggered some details about the order of rule firing, but not much else. If you want a more detailed response you need to be more specific about what version you're comparing to and exactly what rules you're worried about. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] convert timezone to string ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> I know that the server knows that ADT == -0400, and AST == -0300 ...
Other way around isn't it? Unless Canada observes a pretty strange
variety of daylight saving time ;-)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Blank-padding
"Shaun Watts" <[EMAIL PROTECTED]> writes:
> Is there any way to eliminate the blank padding at the end of character
> fields in a table.
Use varchar, or text.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Blank-padding (was: Oracle buys Innobase)
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > I remember that discussion, and I was for the change. However, upon > doing some testing after reading the above, I wonder if the > blank-stripping isn't too aggressive. I have a CHAR(6) field (say, > named Z) that has "abc " in it. Suppose I want to append "x" to Z, > with any leading spaces in Z PRESERVED. (You meant trailing spaces, I assume.) Why exactly would you want to do that? You decided by your choice of datatype that the trailing spaces weren't significant. This gripe seems to me exactly comparable to complaining if a numeric datatype doesn't remember how many trailing zeroes you typed after the decimal point. Those zeroes aren't semantically significant, so you have no case. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should
>> bump the CommandCounter between plan trees, but fails to ...
> Is this something I have to report?
Nah, I fixed it already (only in CVS HEAD though).
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >>> The rule that actually deletes the rows from the underlying has to fire >>> last, since the rows are gone from the view (and hence from OLD) the >>> moment you delete them. > A quote from the postgresql manual: > "But for ON UPDATE and ON DELETE rules, the original query is done after the > actions added by rules. This ensures that the actions can see the > to-be-updated > or to-be-deleted rows" Yes, I know that quote. I wrote it. It's not relevant here because the original query (the delete against the view) is never done at all, since you have a DO INSTEAD rule. What is relevant is the timing of the delete issued against the underlying table, and you have that scheduled to occur too early because the rule that does it is first in alphabetical order. > So, the actual delete should be done after all the rules. And even if > it does delete before anything else, that does not explain why "step2" > is not inserted into the debuglog table. Because the rule converts those inserts into, effectively, INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); and there are no longer any matching OLD rows in the view. (If it didn't act that way then the INSERTs would execute even for a "DELETE WHERE false". If you find any of this surprising or not what you want, you should probably be using triggers not rules.) > Or, that all the rules _are_ > executed when I call the query with "explain analayze". Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should bump the CommandCounter between plan trees, but fails to ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete rule chain stops unexpectedly
Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> which I manipulate with the view "rating_params". The delete rules on this
> view
> act very strangely.
The rule that actually deletes the rows from the underlying has to fire
last, since the rows are gone from the view (and hence from OLD) the
moment you delete them.
In practice, you'd be way better off using an ON DELETE trigger for
these tasks.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Postgres 7.4.9 slow!
Mario Splivalo <[EMAIL PROTECTED]> writes: > I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. > One has 85000 records, and other has 100 records. I've been running > the tests on 7.4.3, Your later message shows 7.4.8. Which is it? > But, now I downloaded postgres 7.4.9, and i'm running the very same > query on the very same database with all the indices and constraints > beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around > 90-110 seconds. You sure it's the very same? The version outputs suggest that these might be two different machines; certainly two very different compilers were used. One thing I'd wonder about is whether both databases were initialized in the same locale. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] casting character varying to integer - order by numeric sort
Bryce W Nesbitt <[EMAIL PROTECTED]> writes:
> SELECT username,last_name
> FROM eg_member ORDER BY username::integer;
> But postgres 7 rejects this with "ERROR: cannot cast type character
> varying to integer".
As a general rule, you need to be more specific than that about which
version you are working with ;-)
You may find that username::text::integer will work, depending on which
7.x this actually is.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] NULL in IN clause
=?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes:
> I have just run this command on 8.0.4 :
> SELECT 'foo' WHERE 0 NOT IN (NULL, 1);
> And it resulted is zero rows.
> Without NULL it is OK.
> Is this a bug, or the standard has such a rule?
This is per spec.
The computation is effectively
NOT (0 = NULL OR 0 = 1)
NOT (NULL OR FALSE)
NOT NULL
NULL
ie, the result is UNKNOWN, which WHERE treats the same as FALSE.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] FULL OUTER JOIN Question
Tyler Kellen <[EMAIL PROTECTED]> writes:
> I have a question about a full outer join returning duplicate rows.
Why do you think they are duplicate? The GROUP BY includes many columns
that you can't see directly in the output ...
Also, you did not show us the actual input data (where's the rows with
trans_item.parent=20116?)
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] owner of data type "areas" appears to be invalid ?
george young writes: > How can I fix this? Re-create the owning user (which you evidently dropped), assigning it sysid 101. PG 8.1 will make it impossible to drop users who still own objects or have permissions ... although that will bring its own set of gotchas ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg, mysql comparison with "group by" clause
"Anthony Molinaro" <[EMAIL PROTECTED]> writes: > An additional gripe is that this isn't a good feature (standard or not). > Oracle doesn't do it. Db2 doesn't do it. You sure about that? It's hard to believe that the SQL committee would put a feature into the spec that neither Oracle nor IBM intended to implement. Those two pretty much control the committee after all ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg, mysql comparison with "group by" clause
>>>> In standard SQL you have to >>>> write GROUP BY ... and list every single column you need from the master >>>> table. This thread seems to have gone off on a tangent that depends on the assumption that the above is a correct statement. It's not. It *was* true, in SQL92, but SQL99 lets you omit unnecessary GROUP BY columns. The gripe against mysql, I think, is that they don't enforce the conditions that guarantee the query will give a unique result. The gripe against postgres is that we haven't implemented the SQL99 semantics yet. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UPDATE Trigger on multiple tables
Ferindo Middleton Jr <[EMAIL PROTECTED]> writes:
> Is it possible to have a single trigger on multiple tables
> simultaneously? Example:
> CREATE TRIGGER emp_cust_stamp BEFORE INSERT OR UPDATE ON employees, customers
> FOR EACH ROW EXECUTE PROCEDURE last_updated_stamp();
No. You can use the same function for multiple triggers, but you have
to CREATE TRIGGER for each table separately.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Update timestamp on update
Jeff Williams <[EMAIL PROTECTED]> writes: > Thanks. Triggers was my first thought, but chapter 35 on Triggers didn't > really indicate a way I could do this easily and scared me with a lot of > c code. Yeah. This is a documentation issue that's bothered me for awhile. The problem is that we treat the PL languages as add-ons and therefore the documentation of the "core" system shouldn't rely on them ... but that leaves us presenting C-code triggers as the only examples in chapter 35. There is a paragraph in there suggesting you go look at the PL languages first, but obviously it's not getting the job done. Anybody have a better idea? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] pg, mysql comparison with "group by" clause
Greg Stark <[EMAIL PROTECTED]> writes: > I think most MySQL users don't stumble on it, they learn it as the way > to handle the common use case when you join a master table against a > detail table and then want to aggregate all the detail records. In > standard SQL you have to write GROUP BY ... and list every single > column you need from the master table. Forcing the database to do a > lot of redundant comparisons and sort on uselessly long keys where in > fact you only really need it to sort and group by the primary key. Actually, if you're grouping by a table's primary key, the SQL99 spec says you shouldn't have to explicitly list the other columns from that table --- they are guaranteed to have unique values per group anyway. This is a single case in the "functional dependency" stuff. That verbiage is incredibly dense and I don't think we want to tackle all of it any time soon, but the primary-key case probably wouldn't be very hard to implement. We really ought to have this in TODO ... I'm sure it's been discussed before. 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] Update timestamp on update
Jeff Williams <[EMAIL PROTECTED]> writes:
> last_status_change timestamp DEFAULT now()
> What I would like is that whenever the status is changed the
> last_status_change timestamp is updated to the current time.
For this you use an ON UPDATE trigger; rules are not a good way to solve
it. See the documentation about triggers. The first example on this
page does it along with a few other things:
http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Text->Date conversion in a WHERE clause
[EMAIL PROTECTED] writes: > SELECT * > FROM > ( > SELECT u.user_id, ud.data > FROM users u, userdata ud > WHERE u.user_id = ud.user_id > AND u.type = 1 > ) subusers > WHERE subusers.data::text::date < now(); > So my question is how does this query ever even SEE the row containing > "052-44-5863"? The sub-query doesn't return that row so I don't see > how it can get this error. BTW, the fallacy in this idea is that the planner pushes WHERE clauses as far down the plan tree as it can. EXPLAIN would show you the actual plan tree, but it's probably along the lines of Join using u.user_id = ud.user_id Scan users u where u.type = 1 Scan userdata ud where ud.data::text::date < now(); If we did not do this, it would pretty much cripple the performance of queries involving views (since a view is nothing but a macro for a sub-select). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Text->Date conversion in a WHERE clause
Collin Peters <[EMAIL PROTECTED]> writes: > WHERE cust3 <> '' > AND cust3::text::timestamp > CURRENT_DATE - interval '1 month' > This results in the error 'ERROR: date/time field value out of range: > "052-44-5863"'. Now that is obviously not a valid date but there > is actually more to the where clause and the first part of it excludes > all rows where the user is not even the correct type, so the row which > includes the field '052-44-5863' should really not even be checked. Your mistake is in supposing that WHERE clauses are guaranteed to be evaluated in a particular order. Such guarantees are made only for a very few specific constructs such as CASE. See http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] ichar
Judith Altamirano Figueroa <[EMAIL PROTECTED]> writes: > ERROR: not exist the function ichar(integer) [ digs in archives... ] Looks like we renamed ichar() to chr() quite some time ago. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] question re. count, group by, and having
"Rick Schumeyer" <[EMAIL PROTECTED]> writes: > Is this a bug or a feature? I'm not sure why I can use 'c' in the order by > clause but not the having clause. pg is much happier with the full "having > count(state) > 5". Actually, referring to any of the output columns in any of the modifier clauses is logically suspect. Original SQL (back around 89 or so) required ORDER BY items to be output column names, thus wiring in an assumption that sorting happens after calculation of the output values, but that is surely not true for any of the other clauses. And it's pretty bogus even for sorting, since you might wish to sort on a value you're not displaying. If we were working in a green field we'd doubtless get rid of the output-column-reference feature entirely. But for backward compatibility's sake we're stuck with allowing ORDER BY items to be simple output column names, per ancient versions of the SQL spec. At one point or another somebody thought it a good idea to propagate that special rule into GROUP BY; which in hindsight was an awful idea. (It's not in the spec. I'm not sure if this is just a Postgres-ism or if we borrowed someone else's bad idea.) But we're stuck with supporting that odd case too, now. We certainly aren't going to add more. > Will this cause count to be evaluated twice? Recent versions of PG are smart enough to merge duplicate aggregates. This isn't necessarily true for other forms of common subexpressions, but it works for aggregate functions. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to delete Large Object from Database?
"Premsun Choltanwanich" <[EMAIL PROTECTED]> writes:
> The code that show below is refered to table and function that I use for =
> kept BLOB (LO).
>
> CREATE TABLE t_data_pic
> (
> "sysid" bigserial NOT NULL,
> data_sysid int8 NOT NULL,
> data_pic lo,
> CONSTRAINT t_data_pic_pkey PRIMARY KEY ("sysid")
> )
> WITH OIDS;
> ALTER TABLE t_data_pic OWNER TO admin;
Why am I not seeing any trigger attached to this table? That lo_manage
trigger is the useful part of contrib/lo --- the separate data type is
mere window dressing.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] UNION index use help
"Dmitri Bichko" <[EMAIL PROTECTED]> writes: > Ok, I'm thoroughly confused. You didn't say which PG version you are using, but if it's something reasonably recent then it should be able to push upper qual conditions down into a UNION. I think you are getting bit by this restriction: * 3. For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can * push quals into each component query, but the quals can only reference * subquery columns that suffer no type coercions in the set operation. * Otherwise there are possible semantic gotchas. You're getting burnt because you're unioning a text with a varchar. Make the column types the same and it'll work better. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Scripting GRANT on functions
"Stewart Ben (RBAU/EQS4) *" <[EMAIL PROTECTED]> writes: > Is there any easy way to script granting privileges to a number of > functions? I've got as far as the following code before realising that > I'll need to pass in the arguments, and the arguments are stored as OIDs > in pg_proc. > Is there any easy way, such as GRANT FUNCTION OID 12345? Coerce the OID to regprocedure to generate a usable name for the function. regression=# select oid from pg_proc limit 5; oid -- 1242 1243 1244 31 1245 (5 rows) regression=# select oid::regprocedure from pg_proc limit 5; oid -- boolin(cstring) boolout(boolean) byteain(cstring) byteaout(bytea) charin(cstring) (5 rows) regression=# regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Use of partial index
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > I'm a little confused about partial indexes. I have a couple of tables, > like this: > CREATE TABLE events ( > event_idINTEGER PRIMARY KEY, > tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), > place_fkINTEGER REFERENCES places (place_id), > event_date CHAR(18) NOT NULL DEFAULT '31', > sort_date DATE NOT NULL DEFAULT '40041024BC', > event_text TEXT NOT NULL DEFAULT '', > sentenceTEXT NOT NULL DEFAULT '' > ); > To this table I have created a partial index: > CREATE INDEX events_born > ON events (tag_type_fk) > WHERE tag_type_fk = 2; > ... > Now, here's an "explain select": > pgslekt=> explain select event_date, place from principals where > person=2 and tag_type=2; >QUERY PLAN > --- > Nested Loop (cost=0.00..23.15 rows=2 width=26) >-> Index Scan using person_event on participants >(cost=0.00..13.63 rows=3 width=4) > Index Cond: (person_fk = 2) > Filter: (is_principal IS TRUE) >-> Index Scan using events_pkey on events >(cost=0.00..3.16 rows=1 width=30) > Index Cond: (events.event_id = "outer".event_fk) > Filter: (tag_type_fk = 2) > (7 rader) > Why doesn't this SELECT use the partial index "events_born" above? Because the plan it did choose is better. events_born could only serve to select the rows with tag_type_fk = 2; assuming there's more than one of those rows, there would be multiple fetches needed to see if any of them have the desired event_id. With this plan it's getting at most one row, by definition (since event_id is the primary key). Had you created the partial index as CREATE INDEX events_born ON events (event_id) WHERE tag_type_fk = 2; then it would be competitive for this query, since the index could effectively handle both constraints not just one. (THe way you did define it, the actual content of the index keys is just dead weight, since they obviously must all be "2". It's often better to define the index column(s) of a partial index as some other column than the one involved in the index predicate...) 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] MOVE in SQL vs PLPGSQL
Richard Huxton writes: > Ah, now I see what you're saying. You're quite right in your suspicions, > "MOVE..." isn't supported for plpgsql cursors. You could probably do > something with EXECUTE and returning a refcursor from a previous > function, but that sounds fiddly. > I must admit, on the odd occasion I want to skip a row, I just FETCH it > and move on. Anyone else? There is something on the TODO list about improving plpgsql's cursor functionality --- there's no reason it shouldn't have MOVE, except that no one got around to it yet. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] using pg_tables and tablename in queries
solarsail <[EMAIL PROTECTED]> writes:
> I have a large number of tables with a common naming convention
> mytable001, mytable002, mytable003 ... mytable00n
> I would like to do a query across all of the tables, however I do not know
> all of the tables before hand, and I do not want to ( cant ) manually
> generate a query like
> select * from mytable001, mytable002, mytable003
> I have a query that returns the names of the tables I want to query:
> select tablename from pg_tables where tablename like 'mytable%'
This looks to me like a situation in which you should rethink your
data design. Those tables should all get merged into one big table,
adding one extra column that reflects what you had been using to
segregate the data into different tables.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] combination of function to simple query makes query slow
"jan aerts (RI)" <[EMAIL PROTECTED]> writes: > My Postgres version is 7.3.4 (on a central server, so I can't upgrade if > that would be one of the suggestions...) 7.3.4 has multiple known data-loss bugs and security issues. If you're dealing with someone who won't upgrade it, find someone else to deal with. At the very least they need to move to 7.3.10 (or as of today, 7.3.11). See http://developer.postgresql.org/docs/postgres/release-7-3-11.html and following pages for reasons why. > I thought that making a function stable or immutable would make it > available for an index search. Your problem isn't the function, it's the IN (SELECT ...) construct. 7.3 is not bright enough to optimize that. (Given that it's a correlated sub-SELECT, I'm afraid later releases aren't either :-(.) You need to find a way of expressing the query without that. My guess is that trying to use a function for this is counterproductive in itself; the table access that's going on inside the function needs to be exposed for optimization in order to get reasonable overall performance. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with function and trigger...
Ian Meyer <[EMAIL PROTECTED]> writes:
> IF TG_OP = 'DELETE' AND OLD.deleted = FALSE THEN
> ERROR: record "old" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
> CONTEXT: PL/pgSQL function "thread_sync" line 2 at if
> What am I failing to understand with this?
We don't guarantee short-circuit evaluation of boolean expressions.
You'll have to break that into two IFs, ie,
IF TG_OP = 'DELETE' THEN
IF ... test on OLD.something ...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
Ferindo Middleton Jr <[EMAIL PROTECTED]> writes:
> Is there some reason why the SERIAL data type doesn't automatically have
> a UNIQUE CONSTRAINT.
It used to, and then we decoupled it. I don't think "I have no use for
one without the other" translates to an argument that no one has a use
for it ...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] redundancy in CHECK CONSTRAINTs
Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: > I have the following table: > CREATE TABLE gyuktnine ( > id SERIAL, > intsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT > int_cannot_equal_ext >CHECK (intsystem != extsystem), > extsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT > ext_cannot_equal_int > CHECK (extsystem != intsystem), > PRIMARY KEY (intsystem, extsystem) > ); > Is this redundant? Yes. I think it's poor style too: a constraint referencing multiple columns should be written as a table constraint not a column constraint. That is, you ought to write CREATE TABLE gyuktnine ( idSERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id), extsystem INTEGER NOT NULL REFERENCES yuksystems(id), PRIMARY KEY (intsystem, extsystem), CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem) ); At least in the earlier versions of the SQL standard, it was actually illegal for a column constraint to reference any other columns. I'm not sure if that's still true in the latest spec. Postgres treats column constraints and table constraints alike, but other SQL databases are likely to be pickier. BTW, is there any actual need for the "id" column here, seeing that you have a natural primary key? 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] Where are user defined functions stored?
Hilary Forbes <[EMAIL PROTECTED]> writes: > Many thanks. Suppose I now want to know if there are any user defined > functions set up in my database? I was rather hoping I could do something > simple like > \df > to get a list of **user** defined functions rather as \dt gives me a list of > my tables and not the complete list of all the system tables as well. Is > there a way of achieving this? You could do something like \df public.* although if your functions are scattered through a bunch of different schemas it'd get a bit tedious. Or try looking at the catalog for yourself, eg select oid::regprocedure, prosrc from pg_proc where pronamespace != (select oid from pg_namespace where nspname = 'pg_catalog'); This essentially implements the rule "system functions are those in pg_catalog". You might want to exclude stuff in information_schema as well. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Where are user defined functions stored?
Hilary Forbes <[EMAIL PROTECTED]> writes:
> How can I easily get to see the definition of a user defined function
> please?
Look in pg_proc.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Issue with UPDATE statement on v8
"Kenneth Hutchinson" <[EMAIL PROTECTED]> writes: > UPDATE t_summary > SETavailability = 7 > WHERE oid = 28245084 > When this query is executed (within a function or without) the database > will simply hang. Is it really hung, or just taking an awfully long time? If the backend is consuming no CPU or I/O then I'd agree it's the former; please look into the pg_locks view to see if you can find out what it's waiting for. If it's the latter, maybe you neglected to create an index on OID? > Has anyone else experienced a similar issue? We'd certainly have heard about it if so. But you haven't provided enough info to let anyone reproduce the problem for investigation. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] delete item[5] from varchar[] array???
Matthew Peter <[EMAIL PROTECTED]> writes:
> How is it possible to delete an item from a single
> dimension varchar[] array?
AFAIR there is no built-in function for this, but it seems like you
could write a generic polymorphic function for it easily enough.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] R-tree and start/end queries
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> There is a built in type for line segments that uses floating point. That
> will probably be usable by you directly unless the integers can can large
> enough that precision is a problem. There is an overlaps operator for the
> geometric types that could be used to answer your sample questions.
However, there's no built-in rtree opclass for that datatype, so he'd
still be stuck with respect to getting indexing support for overlaps
queries.
I think the contrib/seg datatype might help, though the precision issue
is still a possible problem.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Triggers & Conditional Assignment
Neil Saunders <[EMAIL PROTECTED]> writes:
> I've tried to write something along the lines of the following:
> sdate = (NEW.start_date IS NOT NULL) ? NEW.start_date : OLD.start_date;
> edate = (NEW.end_date IS NOT NULL) ? NEW.end_date : OLD.end_date;
> But conditional assignment doesn't seem to be catered for.
The equivalent construct in SQL is CASE.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] CREATE TEMPORARY TABLE ON COMMIT DROP
Luis Sousa <[EMAIL PROTECTED]> writes:
> But how can I create a table using a query and putting ON COMMIT DROP.
You can't. Use INSERT ... SELECT to fill the table, instead.
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] user defined type, plpgsql function and NULL
"Bjoern A. Zeeb" <[EMAIL PROTECTED]> writes: > Is this correct or is it just a "works like that this time but may > change at any time in the future"? The meaning of a NULL for a composite value isn't real well-defined at the moment. I tend to agree that "fbt IS NULL" should yield true in your example, but I think there are/were some implementation reasons why it doesn't. 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] Please help, can't figure out what's wrong with this function...
Moritz Bayer <[EMAIL PROTECTED]> writes:
> I get the following error:
> ERROR: missing .. at end of SQL expression
> I haven't figured out what this message wants to tell me and why it is
> thrown at all.
I think it's telling you that you are using a 7.3 or older server.
Try 7.4 or later --- plpgsql was pretty weak on handling rowtype
variables that far back.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Panic: Page Add Item: Corrupted page pointers
The One <[EMAIL PROTECTED]> writes: > I'm getting this error when try to create a table in Postgresql 7.3, > windows2000: > Panic: Page Add Item: Corrupted page pointers: lower=24258, upper=39318, > special=65421 Apparently you've got at least one corrupt page in at least one system table. I'd recommend pg_dump'ing as much as you can and then recreate the database. If pg_dump doesn't work you could try turning on zero_damaged_pages, and praying that most of what you need is on undamaged pages. It's quite possible though that that will hose the DB completely, so don't do it till you've exhausted the possibilities for pg_dump without it. Consider updating to a more recent PG release while you are recovering... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT: retrieve only 2 rows next to known row
Nikolay Samokhvalov <[EMAIL PROTECTED]> writes: > I don't know the position of this row in result set, but I want to > retrieve 2 rows that are next to this one. In general there is no such thing as "the row next to this one". SQL treats all data sets as unordered, up until the point where you do an explicit ORDER BY for display purposes. So your request is really meaningless unless you phrase it as "I want the rows that come immediately before and after this one in such-an-such an ordering". Once you do that, there is more than one way to solve the problem. For example, if the ordering you care about is on an indexed field, you could do something like SELECT * FROM tab WHERE foo > (SELECT foo FROM tab WHERE condition-to-select-reference-row) ORDER BY foo LIMIT 1 to get the following row, and SELECT * FROM tab WHERE foo < (SELECT foo FROM tab WHERE condition-to-select-reference-row) ORDER BY foo DESC LIMIT 1 to get the prior one (and if you really want just one query result, put these together with UNION ALL). Other solutions that come to mind involve cursors. You haven't told us enough about either the required ordering or the nature of the condition that defines "this row" to really say much about the best solution. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Numeric Columns
miwalsh <[EMAIL PROTECTED]> writes: > I need to make a column that is capable of holding numeric values along with > certain modifiers such as "<" or ">". The column needs to be searchable by > numbers. For example, if someone searches for values lower than 10.0 the > column should return the relevant values. However, the column needs to be > able > to hold values like "<0.05". contrib/seg might do more or less what you're looking for, but none of the standard datatypes will. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] queries problems
Michael Fuhr <[EMAIL PROTECTED]> writes:
> I don't know if there's an easier way to check if an int2 is a
> member of an int2vector, but you could write a function to convert
> an int2vector to an int2 array and then use an "= ANY" expression.
FWIW, as of 8.1 an int2vector *is* an int2 array, so = ANY just works.
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] Indexing an array?
Silke Trissl <[EMAIL PROTECTED]> writes:
> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.
Forget it --- quite aside from indexing inefficiencies, the max size of
an array (or any other single field) is just 1Gb. Don't try to use
arrays to replace tables.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] AGE function
Louise Catherine <[EMAIL PROTECTED]> writes:
> When I execute this statement :
> select AGE(TO_DATE('20041101','mmdd'),
> TO_DATE('19991201','mmdd'))
> at postgre 7.3.3, the result :
> age
> -
> 4 years 11 mons 1 day
With TimeZone set to 'Asia/Jakarta' on a Linux machine, I can reproduce
that behavior in 7.3.* but not 7.4 and later. I believe this is the
relevant change:
2004-12-01 14:57 tgl
* src/backend/utils/adt/timestamp.c (REL7_4_STABLE): Fix
timestamptz_age() to do calculation in local timezone not GMT, per
bug 1332.
and here is a link to the discussion that prompted the change:
http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php
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] Equivalent of Oracle SQL%NOTFOUND in plpgsql
"Bath, David" <[EMAIL PROTECTED]> writes:
>In PL/SQL I can write some like the following:
> fetch cursorblah into blurble;
> if cursorname%NOTFOUND .
See the FOUND variable:
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
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] POSIX Regular Expression question
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Am Montag, 5. September 2005 17:40 schrieb Harald Fuchs: >> I dunno the details of the supported encodings, but is there >> any encoding where 'a-z' includes more or less than 26 letters? > Well, it seems that our regexp library interprets [a-z] as exactly 26 > letters, > but that seems to be a lack of locale support rather than a feature. ISTM that the notation should properly mean "every character that sorts between a and z inclusive". So it's sort order dependent and thus locale dependent --- or at least should be. The locale support in our regexp code is definitely pretty weak at the moment. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Recommendation on bytea or blob for binary data like images
[EMAIL PROTECTED] writes:
> Hi, I'd like to know what the official recommendation is on which binary
> datatype to use for common small-binary size use.
If bytea will work for you, it's definitely the thing to use. The only
real drawback to bytea is that there's currently no API to read and
write bytea values in a streaming fashion. If your objects are small
enough that you can load and store them as units, bytea is fine.
BLOBs, on the other hand, have a number of drawbacks --- hard to dump,
impossible to secure, etc.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > all I want to do is: > CREATE CAST ( 0 AS boolean ) > WITH FUNCTION > AS ASSIGNMENT; > And then each time I try to insert a '0' into a BOOLEAN field, it will > auto convert that (based on my function) to 'f' ... > And I'd need to do a second one for 1 -> 't' ... No, you want one function from smallint to boolean, and the cast the same way. (The cast is really just syntactic sugar for invoking the function.) Depending on what you want this to do, you might have to make the cast IMPLICIT rather than ASSIGNMENT. I'd try ASSIGNMENT first, though, since it's less likely to bite you when you weren't expecting it. 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] insert only if conditions are met?
Henry Ortega <[EMAIL PROTECTED]> writes:
> Is there a way to insert a record only if a certain
> condition is met?
> Something like:
> insert into employee values('lastname','firstname',8) where
> (condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 =
> )
> Is this possible at all with just plain SQL?
Instead of INSERT ... VALUES, use INSERT ... SELECT.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] How do I copy part of table from db1 to db2 (and
Joost Kraaijeveld <[EMAIL PROTECTED]> writes:
> I am afraid that the problem is more complex. The original database
> (which is created with SQL_ASCII) contains invalid byte sequences in
> some columns (target database created with UNICODE):
There is no magic bullet to make bad data better. If the original data
is all in a specific encoding that happens not to be unicode, then you
can get Postgres to translate it for you --- just edit the dump file and
change CLIENT_ENCODING to the real original encoding before reloading.
If, as seems more likely, there's a mishmash of different encodings then
you are in for some pain. At the minimum you'll have to separate out
the rows that are in each encoding so you can pass them through
different conversion processes.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Unwanted nested dollar-quoted constants
Bernard Henry Voynet <[EMAIL PROTECTED]> writes:
> All the text fields are specified using the dollar-quoted string constant
> form that.
This is your mistake to start with. You can not simply stick a couple
of dollar signs around a random string and expect to have a valid
literal, any more than you can stick a couple of quote marks around it
and expect to have a valid literal. Dollar-quoting is not a magic
bullet that will let you forget about escaping data.
I would recommend going back to regular quoted literals and making sure
you double any quotes or backslashes in the data. It's possible to
develop appropriate code for dollar-quoting random text, but it's a lot
harder than it is to escape the data in the old style.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Can EXCEPT Be Used for To Solve This Problem?
"Lane Van Ingen" <[EMAIL PROTECTED]> writes:
> I want to select 2nd oldest transaction from foo (transaction 3).
Can't you just do
select * from foo order by update_time desc offset 1 limit 1
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Problem calling stored procedure
<[EMAIL PROTECTED]> writes: > OPEN cur_overlap FOR SELECT *, pg_class.RELNAME AS table FROM > calendar_entries WHERE (start_date, end_date) OVERLAP (new_start_date, > new_end_date) AND property_id = X AND pg_class.oid = tableoid; > The only thing I can think of is that when the query runs in the psql I get: > NOTICE: added missing FROM-clause entry for table "pg_class" > I understand why this is happening, but don't know how I would go > about re-writing the query to explicitly reference pg_class - I can't > write calendar_entries.table_oid, because that changes the meaning of > the query. How so? It'd be the same as far as I can see. However, you could avoid any explicit use of pg_class by using the regclass type instead: OPEN cur_overlap FOR SELECT *, tableoid::regclass AS table FROM calendar_entries WHERE (start_date, end_date) OVERLAP (new_start_date, new_end_date) AND property_id = X; As far as the reason for the difference between function execution and manual execution: check for unintended variable substitutions. Which words in the query match variable names in the plpgsql function? Are those only the ones you intended? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem with self-made plpgsql-function / casting
Moritz Bayer <[EMAIL PROTECTED]> writes: > ERROR: function public.fc_editlanguage(integer, "unknown", "unknown", > integer) does not exist > > CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name > varchar, kuerzel varchar, active smallint) RETURNS smallint AS The short answer to this is to avoid declaring function arguments as "smallint". When you call this as, say, select fc_editlanguage(42, 'foo', 'bar', 1); the "42" and the "1" are initially typed as integer constants. There's an implicit up-cast from integer to bigint, so the parser has no problem matching the 42 to a bigint parameter, but the down-cast from integer to smallint is not implicit. With the function as written you'd have to cast to smallint explicitly: select fc_editlanguage(42, 'foo', 'bar', 1::smallint); This is enough of a notational pain in the neck that it's easier just to declare the argument as integer. 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] A Table's Primary Key Listing
"D'Arcy J.M. Cain" writes: > That's a good question. The following query does this in a very > unsatisfactory way. Anyone know what the general solution would be? > ... > ( > pg_index.indkey[0]=pg_attribute.attnum OR > pg_index.indkey[1]=pg_attribute.attnum OR > pg_index.indkey[2]=pg_attribute.attnum OR > pg_index.indkey[3]=pg_attribute.attnum OR > pg_index.indkey[4]=pg_attribute.attnum OR > pg_index.indkey[5]=pg_attribute.attnum OR > pg_index.indkey[6]=pg_attribute.attnum OR > pg_index.indkey[7]=pg_attribute.attnum OR > pg_index.indkey[8]=pg_attribute.attnum OR > pg_index.indkey[9]=pg_attribute.attnum > ) In CVS tip you could replace this with "attnum = ANY (indkey)". Unfortunately, most array support doesn't work on int2vector in pre-8.1 releases, so I think you're kinda stuck with the above for now. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is it This Join Condition Do-Able?
Mischa Sandberg <[EMAIL PROTECTED]> writes:
> Anyone care to comment on the third row of output?
I think you mistyped the last INSERT:
> insert into c values(2, 'C2');
> insert into b values(3, 'C3');
I suppose you meant insert into c ...
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] Parentheses in FROM clause and evaluation order.
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Mon, 15 Aug 2005, Dario Bahena Tapia wrote: >> The final result seems to be the same, I just was curious about the >> standard behavior. Does the SQl says something about this execution >> order? > I believe SQL defines the order to pay attention to parens, so A join (B > join C) style clauses result in a "table" being derived from B join C and > another from A joined with that table. SQL only constrains the results, though. It does not forbid the implementation from doing the work in whatever way seems best to it, so long as the results are the same (and "same" does not consider row ordering). For example, SQL92 3.3.4.4 says A conforming implementation is not required to perform the exact sequence of actions defined in the General Rules, but shall achieve the same effect on SQL-data and schemas as that sequence. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with a Pettern Matching Check
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Mon, Aug 15, 2005 at 08:21:23PM -0400, Tom Lane wrote: >> Given that we consider trailing spaces in char(n) to be semantically >> insignificant, would it make sense to strip them before doing the >> regex pattern match? > How standards-compliant would that be? Does the standard specify > what should happen when using SIMILAR TO with a char(n) value? Hmm ... suddenly I'm getting a strong sense of deja vu ... think we've been around this merry-go-round before. SQL99 says ii) The MC LIKE PC is true if there exists a partitioning of MCV into substrings such that: 1) A substring of MCV is a sequence of 0 (zero) or more contiguous s of MCV and each of MCV is part of exactly one substring. 2) If the i-th substring specifier of PCV is an arbitrary character specifier, the i-th substring of MCV is any single . 3) If the i-th substring specifier of PCV is an arbitrary string specifier, then the i-th substring of MCV is any sequence of 0 (zero) or more s. 4) If the i-th substring specifier of PCV is neither an arbitrary character specifier nor an arbitrary string specifier, then the i-th substring of MCV is equal to that substring specifier according to the collating sequence of the , without the appending of characters to MCV, and has the same length as that substring specifier. 5) The number of substrings of MCV is equal to the number of substring specifiers of PCV. Rule ii.4 says that you use the collating sequence associated with the data values, which is where the SQL spec keeps its space sensitivity information --- but the restrictions about not adding space characters and having the same length seem to be intended to prevent use of pad-space-insensitivity to create a match. I think we read this text before, came to the same conclusion, and put in the special operator to make it behave that way. So ... never mind. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem with a Pettern Matching Check
Michael Fuhr <[EMAIL PROTECTED]> writes: > The CHAR(3) specification causes the value to be space-padded, so > '1' becomes '1 ' (the digit "one" followed by two spaces). Actually, we seem to be going out of our way to make this case fail. Given that we consider trailing spaces in char(n) to be semantically insignificant, would it make sense to strip them before doing the regex pattern match? That would happen automatically if we allowed the char(n) value to promote to text --- and the only reason it's not doing so is that there's an extra ~ operator definition that specifically prevents that (bpcharregexeq). I have a feeling that we added that operator definition at some point for backwards compatibility, but it seems a bit odd now. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] insert into / select from / serial problem
"tgh002" <[EMAIL PROTECTED]> writes:
> I am using a insert statement like:
> INSERT INTO newtable
> SELECT field1, field2 FROM anothertable
> newtable structure is: serial, varchar, varchar
> What syntax do I use to insert the serial field?
I think you want to just let it default, which you'd do with, say,
INSERT INTO newtable (fielda, fieldb)
SELECT field1, field2 FROM anothertable
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] about subselect
Louise Catherine <[EMAIL PROTECTED]> writes: > I make a subquery test at postgresql > 7.3.3 and postgresql 8.0.3. > I found that postgresql 8.0.3 can't do this, > while at postgresql 7.0.3 it works quite well : 7.3 says this: NOTICE: Adding missing FROM-clause entry in subquery for table "temp_hasil2" which means that the query is invalid according to the SQL spec. Later versions are just being tougher about enforcing the spec. If you are happy with 7.3's interpretation of what the query means, try adding > Update Temp_hasil2 Set KdPT = A.PTCD, > KdLjr = A.LKRJACD > From (Select A38.NIK,A01.ptcd,A38.Lkrjacd > From AM382 A38 >inner join >ap012 A01 on A38.nojob = A01.nojob > Where A38.tglavd = > (select max(B38.tglavd) from am382 B38, Temp_hasil2 ^ > where A38.nik = B38.nik > and B38.tglavd <= Temp_hasil2.Tanggal) > And A01.tglavd = > (select max(B01.tglavd) from ap012 B01 > where A01.nojob = B01.nojob > and B01.tglavd <= A38.tglavd) > )A > Where Temp_hasil2.NIK = A.NIK; regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: **SPAM** [SQL] Faster count(*)?
[EMAIL PROTECTED] writes: > I believe running count(*) means fulltable scan, and there's no way > to do it without it. But what about some "intermediate" table, with > the necessary counts? There's a fairly complete discussion in the PG list archives of a reasonably-efficient scheme for maintaining such counts via triggers. It wasn't efficient enough that we were willing to impose the overhead on every application ... but if you really NEED a fast count(*) you could implement it. I'd like to see someone actually do it and put up working code on pgfoundry; AFAIK it's only a paper design so far. If you only want a very-approximate count, the best bet is to rely on the planner's estimates, eg regression=# explain select * from tenk1; QUERY PLAN - Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=244) ^ Current best practice is to run the explain and parse out the "rows" figure using a perl (or axe-of-choice) regexp, though we could be persuaded to supply a simpler API if there's enough demand for it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Calling SQL functions that return sets
Chris Mungall <[EMAIL PROTECTED]> writes: > On Mon, 1 Aug 2005, Tom Lane wrote: >> Chris Mungall <[EMAIL PROTECTED]> writes: >>> What are the reasons for deprecating the use of the function in the >>> SELECT clause? >> >> The semantics of having more than one set-returning function in the >> target list are, um, poorly thought out. However, we haven't removed >> the feature because (as you note) there are things you can't do any >> other way. > Is there any roadmap for how this will be handled in future versions? I think it's reasonably safe to say that we won't remove the feature for at least one or two releases after having a 100% substitute (which the present SRF-in-FROM feature is not, as you know). There has been some speculation that the SQL:2003 LATERAL syntax might offer an adequate substitute, but no one is really working on that yet AFAIK. > Coming back to earth, I have a more specific question which follows on > from my initial question. If I have a function 'foo' which takes one > argument and returns a setof some table or composite type, it seems I am > unable to call the function from the select clause. > SELECT foo(1,2); > ERROR: set-valued function called in context that cannot accept a set The present plpgsql implementation only works for SRF-in-FROM. (Which is something that could probably be fixed, but given the development direction we want to go in, it doesn't seem like a very good use of time...) You can do SRF-in-target-list with SQL-language functions or C-coded functions; I'm not certain offhand about the status of the other PL languages. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Calling SQL functions that return sets
Chris Mungall <[EMAIL PROTECTED]> writes:
> What are the reasons for deprecating the use of the function in the
> SELECT clause?
The semantics of having more than one set-returning function in the
target list are, um, poorly thought out. However, we haven't removed
the feature because (as you note) there are things you can't do any
other way.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] bug in information_schema?
Kyle Bateman <[EMAIL PROTECTED]> writes:
> I noticed that it seemed a bit slow to query
> information_schema.view_column_usage. As I look at the code in
> information_schema.sql, I'm not sure why pg_user is referenced twice
> (once without an alias). It looks like we can take out the first
> pg_user and remove the DISTINCT keyword and this improves the efficiency
> significantly.
The unconstrained join against pg_user is clearly unnecessary,
and in fact I took it out a few days ago. I'm not sure whether the
SELECT DISTINCT is still needed --- it might be, if there can be
multiple pg_depend entries linking the same entities.
Peter, any thoughts?
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] REINDEX DATABASE
Chris Browne <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Achilleus Mantzios) writes: >> I am just saying that the common saying "reindex is not needed for >> 7.4+" maybe is not true in all circumstances. > ... > In versions earlier than 7.4, running a REINDEX periodically was > *essential* if you had update patterns consistent with the (remarkably > common) scenario described above. > This reason to reindex (which was the main reason we required > reindexing when using 7.2) has been resolved and gone away in 7.4. > There may be other factors that could mandate REINDEX; as far as I can > tell, the main such factor that remains would be where a table sees > enormous numbers of updates but is not VACUUMed often enough. I think the case that 7.4 resolved is where you have a continually-moving window of index values; for example, an index on a timestamp column in a table where you delete entries older than 30 days. Before 7.4, index pages for timestamps older than 30 days would become empty and then just sit there, with no other way to reclaim them than REINDEX. The case that isn't resolved yet is where you have a usage pattern that causes a lot of index pages to become mostly but not entirely empty. For example, your entries are timestamps, and you have a cleanup process that removes just 99 out of every 100 successive entries. This'll leave you with just a couple of index entries per page, which might not be infinite bloat but it's surely not too efficient. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Different encodings in different DBs in same cluster
Jamie Lawrence <[EMAIL PROTECTED]> writes: > I see: >Since these locale settings are frozen by initdb, the apparent >flexibility to use different encodings in different databases of a >cluster is more theoretical than real. > Does anyone know what "more theoretical than real" mean in this context? It means there are some locales that actively fail (you get inconsistent comparison and sorting behavior) when presented with multibyte data that doesn't match their encoding expectations. IMHO such locale definitions are broken and should be fixed, but they are not under our control. > If I set the locale to C, is it going to work correctly with UTF8 > encoded data? C will work "correctly" for suitably small values of "correctly" --- non-ASCII characters may not sort where you'd wish, and it won't know anything about case-folding for non-ASCII characters. But it will at least give consistent results. When you use a non-C locale, it's best to stick to the encoding that the locale expects. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Error when using array variable
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote: >> You need to initialse the array, Otherwise any value added to the array will >> be null. > This isn't necessary in 8.0, which we can infer is being used because > of the dollar quotes. However, plpgsql wasn't fixed to follow that behavior till 8.0.2 or so. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Can SELECT statements throw an error
Erik Wasser <[EMAIL PROTECTED]> writes: > can SELECT statements throw errors except the followings: > - SQL syntax errors > - connection based errors (database is down/etc...) > - deadlocks > Did I miss an option? Many. Consider select 1 / 0 In general I'd think that most errors in the "data exception", "cardinality violation", "insufficient resources", and "operator intervention" categories are possible. See the error codes appendix for some ideas. And of course, if the SELECT invokes a user-defined function, no holds are barred ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Dumping table definitions
"Mark Fenbers" <[EMAIL PROTECTED]> writes: > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? pg_dump with the -s switch is a much better way ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] problem (bug?) with "in (subquery)"
Luca Pireddu <[EMAIL PROTECTED]> writes:
> On July 15, 2005 08:58, Tom Lane wrote:
>> Ah-hah: this one is the fault of create_unique_path, which quoth
> In any case, it looks like Tom has already found the problem :-) Thanks guys!
On closer analysis, the test in create_unique_path is almost but not
quite completely wrong :-(. Here is the patch against 8.0 branch,
if you need it right away.
regards, tom lane
Index: src/backend/optimizer/util/pathnode.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/util/pathnode.c,v
retrieving revision 1.111
diff -c -r1.111 pathnode.c
*** src/backend/optimizer/util/pathnode.c 31 Dec 2004 22:00:23 -
1.111
--- src/backend/optimizer/util/pathnode.c 15 Jul 2005 17:03:06 -
***
*** 34,40
#include "utils/syscache.h"
! static bool is_distinct_query(Query *query);
static bool hash_safe_tlist(List *tlist);
--- 34,41
#include "utils/syscache.h"
! static List *translate_sub_tlist(List *tlist, int relid);
! static bool query_is_distinct_for(Query *query, List *colnos);
static bool hash_safe_tlist(List *tlist);
***
*** 642,655
pathnode->subpath = subpath;
/*
!* If the input is a subquery whose output must be unique already, we
!* don't need to do anything.
*/
! if (rel->rtekind == RTE_SUBQUERY)
{
RangeTblEntry *rte = rt_fetch(rel->relid, root->rtable);
! if (is_distinct_query(rte->subquery))
{
pathnode->umethod = UNIQUE_PATH_NOOP;
pathnode->rows = rel->rows;
--- 643,683
pathnode->subpath = subpath;
/*
!* Try to identify the targetlist that will actually be unique-ified.
!* In current usage, this routine is only used for sub-selects of IN
!* clauses, so we should be able to find the tlist in in_info_list.
!*/
! sub_targetlist = NIL;
! foreach(l, root->in_info_list)
! {
! InClauseInfo *ininfo = (InClauseInfo *) lfirst(l);
!
! if (bms_equal(ininfo->righthand, rel->relids))
! {
! sub_targetlist = ininfo->sub_targetlist;
! break;
! }
! }
!
! /*
!* If the input is a subquery whose output must be unique already,
!* then we don't need to do anything. The test for uniqueness has
!* to consider exactly which columns we are extracting; for example
!* "SELECT DISTINCT x,y" doesn't guarantee that x alone is distinct.
!* So we cannot check for this optimization unless we found our own
!* targetlist above, and it consists only of simple Vars referencing
!* subquery outputs. (Possibly we could do something with expressions
!* in the subquery outputs, too, but for now keep it simple.)
*/
! if (sub_targetlist && rel->rtekind == RTE_SUBQUERY)
{
RangeTblEntry *rte = rt_fetch(rel->relid, root->rtable);
+ List *sub_tlist_colnos;
! sub_tlist_colnos = translate_sub_tlist(sub_targetlist,
rel->relid);
!
! if (sub_tlist_colnos &&
! query_is_distinct_for(rte->subquery, sub_tlist_colnos))
{
pathnode->umethod = UNIQUE_PATH_NOOP;
pathnode->rows = rel->rows;
***
*** 664,686
}
/*
-* Try to identify the targetlist that will actually be unique-ified.
-* In current usage, this routine is only used for sub-selects of IN
-* clauses, so we should be able to find the tlist in in_info_list.
-*/
- sub_targetlist = NIL;
- foreach(l, root->in_info_list)
- {
- InClauseInfo *ininfo = (InClauseInfo *) lfirst(l);
-
- if (bms_equal(ininfo->righthand, rel->relids))
- {
- sub_targetlist = ininfo->sub_targetlist;
- break;
- }
- }
-
- /*
* If we know the targetlist, try to estimate number of result rows;
* otherwise punt.
*/
--- 692,697
***
*** 755,804
}
/*
! * is_distinct_query - does query never return duplicate rows?
*/
! static bool
! is_distinct_query(Query *query)
{
! /* DISTINCT (but not DISTINCT ON) guarantees uniqueness */
! if (has_distinct_clause(query))
! return true;
! /* UNION, INTERSECT, EXCEPT guarantee uniqueness, except with ALL */
! if (query->setOperations)
{
! SetOperationStmt
Re: [SQL] problem (bug?) with "in (subquery)"
Michael Fuhr <[EMAIL PROTECTED]> writes: > I've been reverse-engineering and simplifying this. Here's something > that I think is close: > CREATE TABLE foo (id integer); > CREATE TABLE bar (id1 integer, id2 integer); > INSERT INTO foo VALUES (1); > INSERT INTO bar VALUES (1, 1); > INSERT INTO bar VALUES (2, 2); > INSERT INTO bar VALUES (3, 1); > SELECT * > FROM foo > WHERE id IN (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); > id > > 1 > 1 > (2 rows) Ah-hah: this one is the fault of create_unique_path, which quoth /* * If the input is a subquery whose output must be unique already, we * don't need to do anything. */ Of course, that needs to read "... unique already, *and we are using all of its output columns in our DISTINCT list*, we don't need to do anything." regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem (bug?) with "in (subquery)"
Luca Pireddu <[EMAIL PROTECTED]> writes:
> So, am I wrong in expecting each strain record to appear only once in the
> result set? Or is there something wrong with PostgreSQL?
Could we see a self-contained example (table definitions and sample data
as a SQL script)? I don't really have time to reverse-engineer a test
case from your description ...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Copy user privileges
"Graham Vickrage" <[EMAIL PROTECTED]> writes:
> I want to keep all the privileges but assign them to a new user.
> What is the best way of doing this?
How about just renaming the old user to a new name?
I don't think we have an ALTER command for that, but an UPDATE on
pg_shadow would get the job done just as well.
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] Make COUNT(*) Faster?
Harald Fuchs <[EMAIL PROTECTED]> writes:
> FOR row IN EXECUTE 'EXPLAIN SELECT * FROM ' || tbl LOOP
> fails with the following message:
> ERROR: cannot open non-SELECT query as cursor
[ checks CVS history... ] Use 8.0.2 or later.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
Steve Wampler <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> If you want something cheap, you could use the same technique the >> planner uses nowadays: take RelationGetNumberOfBlocks() (which is >> guaranteed accurate) and multiply by reltuples/relpages. > Yes - this would be an excellent approximation for my needs! The > solution that Dawid Kuroczko suggested (just call "explain select * > on ..." and parse the result) would be equivalent these days, right? Close enough (the planner actually does some additional heuristic stuff to avoid going crazy on corner cases). 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] Index creation question for expression (col1 || '-' || col2)
Ying Lu <[EMAIL PROTECTED]> writes:
> A question about creating index for the following expression.
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
You need more parentheses:
CREATE INDEX idx_t1 ON test ((col1 || '-' || col2));
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Make COUNT(*) Faster?
Steve Wampler <[EMAIL PROTECTED]> writes:
> So, leave COUNT(*) alone. But it would be very handy to have a
> way to get an approximate table size that is more accurate than is
> provided by a pg_class.reltuples that is only updated on vacuums.
If you want something cheap, you could use the same technique the
planner uses nowadays: take RelationGetNumberOfBlocks() (which is
guaranteed accurate) and multiply by reltuples/relpages. I don't
see anyplace where RelationGetNumberOfBlocks is directly exposed to
users now, but it'd be trivial to code up a couple of C functions to
provide this functionality.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Make COUNT(*) Faster?
Chris Browne <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Varun Mehta) writes: >> If I run an EXPLAIN on this query I can see that it is doing a >> sequential scan, which seems quite needless, as surely this >> information is cached in some secret location. > [ example scenario snipped ] > If there were some "single secret place" with a count, how would you > suggest it address those 78 tuples and 16 transactions that aren't yet > (and maybe never will be) part of the count? It's worse than that: once some of those transactions have committed, the right answer is observer-dependent, since some onlooker transactions may see those guys as committed while others think they are not yet committed. So there could certainly not be just one secret place... There are solutions suggested in the archives, but they all amount to making COUNT(*)-with-no-WHERE-or-GROUP-BY-clause fast at the price of nontrivial distributed overhead for all updates --- overhead that would be paid whether or not the application ever did such a COUNT. That's not a tradeoff we've wanted to make in general. You can implement it yourself via triggers for specific tables that you think it's worth doing for. Also, if an approximate answer is good enough, there are a whole other set of possible solutions. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Prepare plan in plpgsql
Jocelyn Turcotte <[EMAIL PROTECTED]> writes:
> i'm wondering if there is a way to prepare and execute a plan in a
> plpgsql function.
You do not need that because plpgsql automatically caches plans for
SQL statements appearing in a plpgsql function.
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] left joins
"Grant Morgan" <[EMAIL PROTECTED]> writes: > select count(*) > from h left join p using (r,pos) > where h.tn > 20 > and h.tn < 30 > and p.r_order=1 > since it is a left join I though I should get a number no smaller in > the left join than the original unjoined query. It seems to be acting > like an inner join. Well, yeah. The condition p.r_order=1 will return NULL (effectively FALSE) for any row in which p.r_order is NULL, so none of the null-extended rows can survive the WHERE filter, so it's effectively an inner join. Recent versions of PG actively recognize this case and reduce the LEFT JOIN to plain JOIN, but even if we did not do that you'd get the same result. I've heard it claimed that Oracle produces different results; if true, it must have something to do with their rather standards-challenged interpretation of NULL ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 'show full processlist' in postgres?
Erik Wasser <[EMAIL PROTECTED]> writes:
> But 'current_query' is still always empty... B-(
The pg_stats views lag reality by a certain amount, so checking for your
own query is generally not gonna work. Try starting a long-running
query in another session.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Foreign key pg_dump issue and serial column type
"Vsevolod (Simon) Ilyushchenko" <[EMAIL PROTECTED]> writes: > However, when I pg_dump the database and import it on another server, > the tables are exported alphabetically, so when the 'people_roles' table > is created with its foreign keys, the table 'roles' does not exist yet. > Thus, the foreign key creation fails. Is there a way around it? Are you sure you are using 8.0 pg_dump? That's a longstanding deficiency in older versions, but 8.0 is not supposed to have a problem with it. If you're sure it's an up-to-date pg_dump, could you provide a complete test case (ie, a script to create a database that pg_dump has trouble with)? > 2. I've just discovered the 'serial' column type and tried to do this: > alter table people_roles alter column people_roles_code type serial; > To my surprise, it fails: > ERROR: type "serial" does not exist Serial isn't quite a true type, and so it doesn't work in every context that you might think. It'd probably make sense for "alter column type" to accept it, but for now what you gotta do is create a sequence and set the column default manually. 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] ENUM like data type
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Rod Taylor wrote: >> Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some. > Not really. A domain doesn't create a new type. If you base your enum > domains on the text type, as would usually be the case, then nothing > stops you from using, say, text concatenation operators and the like. > I suppose in practice this won't matter too much, but it can't be > called a clean design. What you'd really need is a way to create a > distinct type. SQL has a feature for that, but PostgreSQL hasn't > implemented it. It's not that hard to make your own type using the builtin textin and textout functions, and then add just the functions you wish to provide. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] empty view, replace view, column type change?
Bruce Momjian writes: > Tom Lane wrote: >> but it doesn't seem to me to follow from what the spec says that we need >> to explicitly cast the result of now() to six places. As long as it's >> coming from gettimeofday it can't have more than 6 places anyway, and so >> we might as well save the extra coercion step. (The parser *will* tack >> on a separate coercion function call when presented with this parse >> tree.) >> >> In short, I'm inclined to remove the above-quoted lines, and similarly >> for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? > Agreed. That "6" bothered me too when I was cleaning up the timestamp > code a while back. Done. On re-reading the spec, I see that the default of six places is intended to apply to ALL occurrences of timestamp --- that is, they intend CREATE TABLE foo (ts timestamp); to mean CREATE TABLE foo (ts timestamp(6)); We have deliberately decided not to do that, on a fairly consistent basis --- numeric and varchar don't have any fixed precision limit either if you don't specify. So these functions were out of step with the rest of the system, and removing their explicit precision limit is indeed a sane way of improving consistency. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] empty view, replace view, column type change?
Rod Taylor <[EMAIL PROTECTED]> writes: > On Thu, 2005-06-23 at 22:27 +0200, Markus Bertheau wrote: >> Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisa³(a): >>> The data types are different, as one has the timestamp to (6) decimal >>> places after seconds. >> >> That's strange. I explicitly specified ::TIMESTAMP on both the view and >> the table. Is that not unambiguous? > LOCALTIMESTAMP is probably more specific, so it folds the length in. > If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). Yeah, this is exactly what's happening --- if you look at gram.y, LOCALTIMESTAMP is expanded to "'now'::text::timestamp(6)". I am unconvinced that the (6) is a very good idea though. The code says /* SQL99 mandates a default precision of 6 for timestamp. * Also, that is about as precise as we will get since * we are using a microsecond time interface. * - thomas 2001-12-07 */ d->typmod = 6; but it doesn't seem to me to follow from what the spec says that we need to explicitly cast the result of now() to six places. As long as it's coming from gettimeofday it can't have more than 6 places anyway, and so we might as well save the extra coercion step. (The parser *will* tack on a separate coercion function call when presented with this parse tree.) In short, I'm inclined to remove the above-quoted lines, and similarly for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] optimizer, view, union
Markus Bertheau <[EMAIL PROTECTED]> writes: > Can pg transform > SELECT * FROM ( > SELECT 'foo' AS class, id FROM foo > UNION ALL > SELECT 'bar' AS class, id FROM bar > ) AS a WHERE class = 'foo' [ experiments... ] Yes, if you spell it like this: regression=# explain SELECT * FROM ( regression(# SELECT 'foo'::text AS class, id FROM foo regression(# UNION ALL regression(# SELECT 'bar'::text AS class, id FROM bar regression(# ) AS a WHERE class = 'foo'; QUERY PLAN - Append (cost=0.00..105.60 rows=4280 width=4) -> Subquery Scan "*SELECT* 1" (cost=0.00..52.80 rows=2140 width=4) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) -> Subquery Scan "*SELECT* 2" (cost=0.00..52.80 rows=2140 width=4) -> Result (cost=0.00..31.40 rows=2140 width=4) One-Time Filter: false -> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=4) (7 rows) If unadorned, the literals get caught up in some type-conversion issues. (You don't really want them in the output of a view anyway; "unknown" type columns are bad news.) 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] after delete trigger behavior
Stephan Szabo <[EMAIL PROTECTED]> writes: > Is there anything we have right now that will handle this kind of thing > without requiring either updating all the counts after a deletion in a > statement trigger or once per row updating all the counts for records with > the same "a" (doing something like make a sequence and using it in a > subselect matching keys)? The best thing I can think of is your first idea, ie, renumbering all the rows in a statement-level AFTER DELETE trigger. Something like (untested) DECLARE rec record; n integer := 1; BEGIN FOR rec IN SELECT * FROM table WHERE <> ORDER BY sort_order LOOP IF rec.sort_order != n THEN UPDATE table SET sort_order = n WHERE <>; END IF; n := n + 1; END LOOP; END; Ugly as this is, it's at least linear in the number of rows to be changed; the originally proposed trigger was O(N^2) in the number of rows affected, and would surely be intolerably slow for multiple deletes in a reasonably sized table. Given an index on the grouping columns plus sort_order, it could even be reasonably fast (don't forget to make the ORDER BY match the index). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] after delete trigger behavior
Stephan Szabo <[EMAIL PROTECTED]> writes: >> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE >> PROCEDURE resort_test1(); > I think this will work in an after delete trigger, but not in a before > delete trigger (and seems to in my tests). I'm not sure what the spec says > about the visibility of rows in cases like this. Well, the actual effect is that the first trigger's UPDATE changes all the rows that the DELETE might later delete, thus overriding the delete. (A query cannot modify rows already modified by commands started later in the same transaction, such as commands issued by triggers fired by the query itself.) Depending on the order that the DELETE hits the rows in, there might be more than one row that can get processed before the UPDATEs have touched all remaining rows, so this is all pretty messy and not to be relied on. I suspect that if you read the spec carefully it would want a "triggered data change violation" error raised here. My advice is not to use a BEFORE trigger for this. Even an AFTER trigger will have some pretty significant problems with this, I'm afraid, because of the uncertainty about the order in which the rows are deleted (and hence the order in which the trigger instances fire). For instance, suppose you delete the rows with c=1 and c=2, and they get visited in that order. The UPDATE for c=1 will update the row currently having c=3 to c=2 ... whereupon that row will NOT be seen as an update candidate by the UPDATE for c=2. (You could work around that case by using ">= OLD.c" instead of "> OLD.c", but it could still fail with more than 2 rows being deleted.) The proposed trigger only works cleanly if the rows are deleted in decreasing order of c, and there's no very easy way to guarantee that. 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] Putting an INDEX on a boolean field?
"Erik Aronesty" <[EMAIL PROTECTED]> writes: > Should I start looking to figure out why the optimizer didn't figure out > that it should be doing this sort of thing? It looks to me that the problem is that convert_IN_to_join() is not being smart about where to attach the IN's subselect to the join tree. It's just adding it to the top FROM-expression, so that the join tree is effectively ((sites left join quota) IN-join usersites) and since we don't currently allow any rearrangement of outer joins, this cannot be rearranged into ((sites IN-join usersites) left join quota) as you'd like. The really clean solution to this would be to implement logic about when it is safe to rearrange the join order of outer joins. But I think that's a fairly hard problem in general. A shorter-term solution might be possible by teaching convert_IN_to_join() to attach the IN subselect further down in the join tree, using logic similar to what we use to decide where ordinary WHERE quals can bubble down to. 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] WHY transaction waits for another transaction?
Vilinski Vladimir <[EMAIL PROTECTED]> writes: > During the execution of transaction Nr:10295 (PID:18430) one new transaction > with > Nr:10339 (PID:18431) starts, that writes one record into the table. But this > new > transaction never stops, because it tries to set one ShareLock to its > parrent transaction Nr:10295. > My problem is, how can i found out - WHY the second transaction waits > for end of first transaction? Presumably it is blocked on a row lock that the first transaction holds. There isn't any really good way to find out exactly which row is involved in existing releases (8.1 will be better). If you're desperate you could go in with a debugger, but it's probably easier to reason it out, because there are not that many possibilities. One way to get this would be if the two transactions tried to update the same rows in different orders. But I think that would be a deadlock condition in Oracle too, so if your code worked on Oracle that's probably not it. The more likely suspect is a foreign key conflict. Are both transactions inserting/updating rows that could reference the same row(s) in a master table? PG takes a row lock on the referenced row (to make sure it won't disappear before commit) and so you can get deadlocks in the master table. PG 8.1 will have sharable row locks and use those for foreign key interlocks, so this problem should essentially disappear in 8.1. At the moment, the best workaround I know of is to make the foreign key checks all deferred, so that they're not checked until the transaction is about to commit. This is not bulletproof, but because it considerably reduces the time window for a conflict, it may do as a workaround until 8.1 is ready. regards, tom lane ---(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] partial index on non default tablespace syntax
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> Looks like its not possible to specify tablespace of an index with a
> where clause,
Hm?
regression=# create table foo(f1 int, f2 int);
CREATE TABLE
regression=# create index fooi on foo (f1) tablespace pg_default where f2 < 0;
CREATE INDEX
(I hadn't bothered to make a tablespace to test with, but the point
is the syntax is fine.)
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Putting an INDEX on a boolean field?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> Does that make sense? Would it ever get used?
It could get used if one of the two values is far less frequent than the
other. Personally I'd think about a partial index instead ...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
