Re: [SQL] iceberg queries
> > Does PostgreSQL optimizer handle iceberg queries well? > What do you mean by "iceberg query" ? I've never heard this term. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg_views
> > I'm wondering if PostgreSQL actually reparses the view definition on > each invocation or if it stores the required information in some > accessible place. > The documentation says: Whenever a query against a view (i.e. a virtual table) is made, the rewrite system rewrites the user's query to a query that accesses the base tables given in the view definition instead. > > My goal is to take a view name as input and output the tables and > columns composing the view. > I very much doubt this is possible, unless you step deep into the parser defined in gram.y and scan.l, which is as you mentioned beyond your scope. Regars, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL99/SQL92 Grammar
See the developers FAQ for URL's. --- Les Hazlewood wrote: > > Does anyone have a definitive BNF grammar of SQL99 or SQL92? (I'd prefer 99 > but I'll take what I can get ;) > > I'm trying to make a simplified XML to SQL translator, and I need the > grammar to do so (not Postgres's grammar...the standard grammar). It must > be available somewhere, because Postgres developers need to refer to it at > some point... > > If the grammar is accompanied with explanations (like Postgres does in its > online manual, or like Oracle in theirs), that would be bestbut again, > I'll take what I can get. > > I tried looking on the ANSI website, but with no results. Damn > bastards...how can they make a standard and then charge for you to see it? > What the hell is the point if the 'standard' is not open to everyone who > might need it? What about the students and joe-blows in this world (like > me) who don't work for a company and who don't have a ton of money to spend > on stupid ANSI subscriptions.? > > Oh sorry...I got off on a rant > > Regardless, I'd love the grammar or a link to where I can view it for > _free_. > > Can anyone help a guy out? Thanks! > > Les Hazlewood > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] iceberg queries
It is a query that looks like SELECT target1, target2... targetn, SUN(t.qty) FROM Table t GROUP BY target1 HAVING SUM(t.qty)>=10 You can replace SUM(t.qty)>=10 with other aggregate constraints. - Original Message - From: Christoph Haller To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 04, 2003 3:39 AM Subject: Re: [SQL] iceberg queries > > Does PostgreSQL optimizer handle iceberg queries well? > What do you mean by "iceberg query" ? I've never heard this term. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] iceberg queries
On Tue, Feb 04, 2003 at 09:08:56 -0500, Wei Weng <[EMAIL PROTECTED]> wrote: > It is a query that looks like > > SELECT target1, target2... targetn, SUN(t.qty) > FROM Table t > GROUP BY target1 > HAVING SUM(t.qty)>=10 > > You can replace SUM(t.qty)>=10 with other aggregate constraints. There were some recent changes to allow groups to use hashes that may help for queries like this. This can save a sort step or using an index scan. In theory you might be able to make some other speed ups by taking advantage of properties of specific aggregate functions (in particular that several common ones monotonicly increase or decrease as they are being calculated) and if doing an index scan on the fields used for group you might be able to skip a lot of rows. I expect that this situation would come up pretty rarely though. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_views
Lex Berezhny wrote: > > hi, > > I'm trying to write some code that can analyze the database structure > and i need a way to discover the composition of a view (the tables and > table.column info). > > I've managed to do much of this by querying the pg_views for the > definition and literally parsing the SQL myself, but obviously that has > many limitations unless I impliment a complete SQL parser and that's > beyond the scope of what I want :-) > > I'm wondering if PostgreSQL actually reparses the view definition on > each invocation or if it stores the required information in some > accessible place. > > My goal is to take a view name as input and output the tables and > columns composing the view. Don't forget that a view's columns can contain complex expressions instead of simple table.column references. The place where the real information about views is stored is pg_rewrite. pg_views is a view itself, using a function that parses back the rewrite rule into a human readable format. There are people around who can read the querytree format stored in pg_rewrite as well. So parsing pg_views output would be ... er ... parsing a query string that was reconstructed from a parsetree that resulted from parsing a query string ... that doesn't sound like the right thing to do. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] iceberg queries
Christoph Haller wrote: > > > > > Does PostgreSQL optimizer handle iceberg queries well? > > > What do you mean by "iceberg query" ? > I've never heard this term. Iceberg queries compute one or more aggregate functions to find aggregate values above a specified threshold. A typical iceberg query would be SELECT a, count(a) FROM tab GROUP BY a HAVING count(a) >= 100; This base form can easily be made more complicated by doing self joins and the like. This type of query is often found in market research, data warehousing and search engines. As to the original question, if an index is available that returns the rows in the sort order of the GROUP BY clause, PostgreSQL defaults to an index scan, otherwise it will do a sort of the rows matching an optional WHERE clause. This sorted set is then grouped and aggregated and filtered by the HAVING clause after aggregation. It is well known that this approach does not scale well for large data sets. But in contrast to a specialized statistical software, PostgreSQL has to answer the query precisely. So sampling or bucket methods aren't options. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] iceberg queries
Jan Wieck <[EMAIL PROTECTED]> writes: > As to the original question, if an index is available that returns the > rows in the sort order of the GROUP BY clause, PostgreSQL defaults to an > index scan, otherwise it will do a sort of the rows matching an optional > WHERE clause. This sorted set is then grouped and aggregated and > filtered by the HAVING clause after aggregation. Note that as of 7.4, the planner will probably pick hashed aggregation rather than sort-based aggregation, if it can predict that the number of groups will not be too large for a hash table to fit in memory. This means we can do a seqscan (or, perhaps, an indexscan to match WHERE conditions) and avoid sorting. So I expect performance on this type of query to be a good deal better in 7.4. There are a few benchmark comparisons in the pghackers archives a couple months back. 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] pg_views
Jan Wieck <[EMAIL PROTECTED]> writes: > Lex Berezhny wrote: >> My goal is to take a view name as input and output the tables and >> columns composing the view. > Don't forget that a view's columns can contain complex expressions > instead of simple table.column references. Yes. This problem is not simple. > So parsing pg_views output would be ... er ... parsing a query string > that was reconstructed from a parsetree that resulted from parsing a > query string ... that doesn't sound like the right thing to do. As of 7.3, at least some of what Lex wants could probably be extracted from the pg_depend entries for the view's select rule. For example, given regression=# create view vv as select unique1, hundred from tenk1; I see these entries in pg_depend: regression=# select * from pg_depend where objid=578707; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -++--++--+-+- 16410 | 578707 |0 | 1259 | 578705 | 0 | i 16410 | 578707 |0 | 1259 | 578705 | 0 | n 16410 | 578707 |0 | 1259 | 443421 | 1 | n 16410 | 578707 |0 | 1259 | 443421 | 7 | n (4 rows) The first two just link back to the owning view (hm, why are we making two entries for that?) but the other two show that the view depends on columns 1 and 7 of table 443421, ie, tenk1. This won't tell you exactly how the view uses those columns, only that they are referenced; but it might be good for something. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Serialized Transaction Locking Issues
Hello, I'm currently in the midst of working on a serializable transaction which drops indexes on several tables, does a bulk copy, and rebuilds the indexes. Based on what I've read it seemed as though I'd be able to concurrently execute read only queries against these tables, returning results based on the table snapshot from before the serialized transaction began. However, this doesn't work. A simple read-only select query waits until the serialized transaction is finished before returning results. Based on the user documentation, specifically (http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-locking.html#LOCKING-TABLES), it would seem that the only issue in PostgreSQL that could block a simple select call would be an ACCESS EXCLUSIVE lock, which is acquired only by DROP TABLE, ALTER TABLE, and VACUUM FULL, none of which I'm using. In fact, I've noticed this exact behavior with DROP INDEX. Please excuse my futile attempt to outline two concurrent transactions here: testdb=# \d trans_test Table "public.trans_test" Column | Type | Modifiers - ---++--- val integer Indexes: idx_trans_test btree(val) testdb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET [TRANSACTION 1] BEGIN; BEGIN [TRANSACTION 1] SELECT * FROM trans_test; val - - 1 2 [TRANSACTION 2] SELECT * FROM trans_test; val - - 1 2 [TRANSACTION 1] DROP INDEX idx_trans_test; DROP INDEX [TRANSACTION 2] SELECT * FROM trans_test; ... (Waiting) [TRANSACTION 1] COMMIT; COMMIT (TRANSACTION 2 returns after TRANSACTION 1 COMMIT) val - - 1 2 So is this a glitch or am I missing some nuance of a serializable transaction? In either case I'm eager to figure out whats actually going on. Thanks, -justin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] iceberg queries
Tom Lane wrote: > > Jan Wieck <[EMAIL PROTECTED]> writes: > > As to the original question, if an index is available that returns the > > rows in the sort order of the GROUP BY clause, PostgreSQL defaults to an > > index scan, otherwise it will do a sort of the rows matching an optional > > WHERE clause. This sorted set is then grouped and aggregated and > > filtered by the HAVING clause after aggregation. > > Note that as of 7.4, the planner will probably pick hashed aggregation > rather than sort-based aggregation, if it can predict that the number > of groups will not be too large for a hash table to fit in memory. > This means we can do a seqscan (or, perhaps, an indexscan to match > WHERE conditions) and avoid sorting. So I expect performance on this > type of query to be a good deal better in 7.4. There are a few > benchmark comparisons in the pghackers archives a couple months back. > > regards, tom lane If it can predict. I guess the question was asked because one expects performance problems. >From that I conclude that the amount of data is significant in this particular case. That does not necessarily but usually mean a large number of unique groups. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Serialized Transaction Locking Issues
justin <[EMAIL PROTECTED]> writes: > I'm currently in the midst of working on a serializable transaction > which drops indexes on several tables, does a bulk copy, and rebuilds > the indexes. Based on what I've read it seemed as though I'd be able to > concurrently execute read only queries against these tables, Nope, because "drop index" obtains an exclusive lock on the table. (If it didn't, some other query might try to *use* the index, which would create a bit of a problem if the DROP committed midway through.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Inserting a tab character
I have a table which defines various possible file delimiters (CHAR(1) NOT NULL), for the moment it'll only contain comma and tab. Inserting a comma is easy, but inserting a tab is proving somewhat more difficult. How do I do it in 'psql'? Luke Pascoe. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Inserting a tab character
Hi Luke. On Wednesday, February 5, 2003, at 02:39 PM, Luke Pascoe wrote: I have a table which defines various possible file delimiters (CHAR(1) NOT NULL), for the moment it'll only contain comma and tab. Inserting a comma is easy, but inserting a tab is proving somewhat more difficult. How do I do it in 'psql'? insert into test values( '\t' ); appears to work... Matt. ---(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] Inserting a tab character
--- Luke Pascoe <[EMAIL PROTECTED]> wrote:
> I have a table which defines various possible file
> delimiters (CHAR(1) NOT
> NULL), for the moment it'll only contain comma and
> tab. Inserting a comma is
> easy, but inserting a tab is proving somewhat more
> difficult.
>
> How do I do it in 'psql'?
>
--> Try using '\t' for tab.
Example :
INSERT INTO table1(f1) values ('\t');
I'm not sure if inserting a TAB character will
cause some side-effects for commands like COPY FROM /
TO since these commands use tab to delimit fields.
Hope that helps,
ludwig
Hope that helps,
ludwig.
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
