Re: [HACKERS] Fixing PKs and Uniques in tablespaces
Christopher Kings-Lynne wrote: > > Also, since I checked and it seems that our syntax for putting > > tables an d indexes in tablespaces at creation time is identical to > > oracle's, perhaps we should copy them on constraints as well. > > Since we're getting close to beta, can we have consensus on what I'm > to do about this? I think it's OK if you can do the Oracle syntax (or some other established syntax) without reserving the key word TABLESPACE. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql listTables
I know, but I don't get too many opportunities to contribute... :) Just figured I'd mention it. -tfo On Jul 23, 2004, at 4:06 PM, Tom Lane wrote: "Thomas F.O'Connell" <[EMAIL PROTECTED]> writes: I realize this is a trivial issue, but it seems like logic could be added to the CASE statement to prevent irrelevant SELECT material from being output. Hardly seems worth the trouble ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql listTables
"Thomas F.O'Connell" <[EMAIL PROTECTED]> writes: > I realize this is a trivial issue, but it seems like logic could be > added to the CASE statement to prevent irrelevant SELECT material from > being output. Hardly seems worth the trouble ... 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: [HACKERS] ffunc called multiple for same value
Ian Burrell <[EMAIL PROTECTED]> writes: > We are doing things in the aggregates that make them troublesome when > called the ffunc is called multiple times. The state structure uses a > lot of memory for intermediate work. The memory needs to be freed as > soon as possible otherwise there is a danger of running of out memory. Possibly you should just force enable_hashagg off, if you are concerned about memory usage. ISTM that running multiple transvalue calculations in parallel is a bad idea from the start, if you are feeling that tense about the amount of memory that will be chewed up by just one. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] psql listTables
In examining the output of psql -E to get some templates for some
queries I'm developing, I noticed in describe.c that there is logic to
inform the final IN clause that gets printed for relkind but no similar
logic for the CASE clause.
Here's what I get from a \d in 7.4.1:
SELECT n.nspname as "Schema", c.relname as "Name",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Such that the IN clause for c.relkind seems to preclude two of the
options ('i' and 's') in the CASE.
I realize this is a trivial issue, but it seems like logic could be
added to the CASE statement to prevent irrelevant SELECT material from
being output.
Couldn't the SELECT clause include if(showTables), etc.?
-tfo
---(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: [HACKERS] ffunc called multiple for same value
Tom Lane wrote: So I'm rather inclined to define this behavior as "not a bug". The fact that you're complaining seems to indicate that your ffunc scribbles on its input, which is bad programming practice in any case. Ordinarily I would not think that an ffunc should have any problem with being executed repeatedly on the same final transvalue. (If you really want to do things that way, maybe your code should take responsibility for keeping a flag to execute just once, rather than pushing the cost onto everybody.) We are doing things in the aggregates that make them troublesome when called the ffunc is called multiple times. The state structure uses a lot of memory for intermediate work. The memory needs to be freed as soon as possible otherwise there is a danger of running of out memory. It is possible to store the resuts on the first ffunc call, free the intermediate state, return the results on later calls, and make sure the free only happens once. The docs didn't make clear that calling ffunc multiple times could happens so we did not code to allow it. - Ian ---(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: [HACKERS] ffunc called multiple for same value
Tom Lane wrote: So I'm rather inclined to define this behavior as "not a bug". The fact that you're complaining seems to indicate that your ffunc scribbles on its input, which is bad programming practice in any case. Ordinarily I would not think that an ffunc should have any problem with being executed repeatedly on the same final transvalue. (If you really want to do things that way, maybe your code should take responsibility for keeping a flag to execute just once, rather than pushing the cost onto everybody.) Comments anyone? As someone who makes use of C language aggregate functions, I agree with your analysis, so long as the fact that an ffunc may be invoked more than once is well documented, (i.e. an SGML section might be nice.) Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ffunc called multiple for same value
Ian Burrell <[EMAIL PROTECTED]> writes: > I posted a message a couple weeks ago abou having a problem with a > user-defined C language aggregate and the ffunc being called multiple > times with the same state. I came up with a test case which shows the > problem with plpgsql functions. It occurs with an aggregate in an inner > query, when a nested loop is used. I looked into this and found that the unexpected behavior occurs only when a HashAggregate plan is used. If you force a GroupAggregate to be used (set enable_hashagg = false), then you get one series of sfunc calls and one ffunc call, per group per scan of the inner relation. In the HashAgg code, the series of sfunc calls is executed only once per group, with the final transvalue being stored in the hash table. The ffunc will be re-evaluated on each traversal of the hash table for output --- which could be multiple times, if the grouped table is used as the inside of a nestloop, as in this example. I can imagine fixing this by having the HashAgg code replace the final transvalue in the hash table with the ffunc result value. It would not be a whole lot of additional code, but it would make things noticeably more complicated in what's already a rather complex bit of code (mainly because transvalue and result could be different datatypes). Probably the worst objection is that with pass-by-reference result types, an additional datumCopy step would be needed to stash the result in the hash table (and there'd be an extra pfree, too). That would slow things down for everybody, with no gain unless the HashAgg result is in fact read multiple times. A different alternative which would be much lower-impact in terms of code changes would be to change ExecReScanAgg() to always throw away the hash table, even if it knows that the input data has not changed. While this would avoid any time penalty for those not making use of repeated scans, it would be a huge penalty for those that are, so it hardly seems like an appealing choice either. So I'm rather inclined to define this behavior as "not a bug". The fact that you're complaining seems to indicate that your ffunc scribbles on its input, which is bad programming practice in any case. Ordinarily I would not think that an ffunc should have any problem with being executed repeatedly on the same final transvalue. (If you really want to do things that way, maybe your code should take responsibility for keeping a flag to execute just once, rather than pushing the cost onto everybody.) Comments anyone? 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])
[HACKERS] Can I determine the server name from PGresult?
I have some libraries that serve as libpq wrappers. One function has the PGresult structure pointer passed to it. I'd like to be able to determine the host name from within the function, but I don't have the PGconn pointer to use PQhost(). Is there any workaround to this? Can I somehow pass PGresult to PQhost() or some similar function. Way back before PG 7.0, I would have been able to link to PGconn from within PGresult. Now it seems that the PGresult structure is purposefully hidden from the programmer. TIA, -Tony ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] patch for allowing multiple -t options to pg_dump
His patch has multiple -t options and -T. --- Andreas Joseph Krogh wrote: -- Start of PGP signed section. > On Tuesday 20 July 2004 05:54, Bruce Momjian wrote: > > Looks like someone else also just submitted the same patch, except with > > a -T option to exclude tables. I will consider that version instead. > > I can certainly see how that -T option is valuable, but I think multiple -t > options also make sense if you just want to dump 2 or 3 tables in a database > containing lots of tables. Why not include both? > > -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > Senior Software Developer / Manager > gpg public_key: http://dev.officenet.no/~andreak/public_key.asc > +-+ > OfficeNet AS| - a tool should do one job, and do it well. | > Hoffsveien 17 | | > PO. Box 425 Sk?yen | | > 0213 Oslo | | > NORWAY | | > Phone : +47 22 13 01 00 | | > Direct: +47 22 13 10 03 | | > Mobile: +47 909 56 963 | | > +-+ -- End of PGP section, PGP failed! -- 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: [DOCS] [HACKERS] Tutorial
Double postings are a PITB
- Forwarded message from elein <[EMAIL PROTECTED]> -
Date: Thu, 22 Jul 2004 21:31:37 -0700
From: elein <[EMAIL PROTECTED]>
To: Robert Treat <[EMAIL PROTECTED]>
Cc: Joe Conway <[EMAIL PROTECTED]>,
elein <[EMAIL PROTECTED]>,
David Fetter <[EMAIL PROTECTED]>,
[EMAIL PROTECTED]
Subject: Re: [DOCS] [HACKERS] Tutorial
Mail-Followup-To: Robert Treat <[EMAIL PROTECTED]>,
Joe Conway <[EMAIL PROTECTED]>, David Fetter <[EMAIL PROTECTED]>,
[EMAIL PROTECTED]
In-Reply-To: <[EMAIL PROTECTED]>
User-Agent: Mutt/1.3.22.1i
Postgresql is not simply a relational database. It is an
OBJECT relational database. It was designed to be so from
the start. To pretend it was designed otherwise is to
deny its design and heritage and the original intent of
the the project. c.f. the postgres papers, stonebraker, et.al.
And like tom said, "who said inheritance is not relational."
It need not break codds rules.
--elein
On Thu, Jul 22, 2004 at 10:40:45PM -0400, Robert Treat wrote:
> On Thursday 22 July 2004 21:07, Joe Conway wrote:
> > elein wrote:
> > > I like inhertitance, but believe that the usefulness
> > > of our implementation is limited and so the documentation
> > > should focus on other areas.
> >
> > +1
> >
>
> +1/2 (Since I don't like inheritence)
>
> IMHO we ought to try to keep the _tutorial_ free of things that are generally
> considered against relational design. If we must keep them, move them into
> thier own section and lable them accordingly.
>
> Robert Treat
> ---
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
- End forwarded message -
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] patch for allowing multiple -t options to pg_dump
On Tuesday 20 July 2004 05:54, Bruce Momjian wrote: > Looks like someone else also just submitted the same patch, except with > a -T option to exclude tables. I will consider that version instead. I can certainly see how that -T option is valuable, but I think multiple -t options also make sense if you just want to dump 2 or 3 tables in a database containing lots of tables. Why not include both? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| - a tool should do one job, and do it well. | Hoffsveien 17 | | PO. Box 425 Skøyen | | 0213 Oslo | | NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgp9kQXxPKxFN.pgp Description: PGP signature
Re: [HACKERS] Wrong index choosen?
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > In this plan it estimates to get 481 but it got 22477. So the estimation > was very wrong. You can increase the statistics tarhet on the login_time > and it will probably be better (after the next analyze). Given the nature of the data (login times), I'd imagine that the problem is simply that he hasn't analyzed recently enough. A bump in stats target may not be needed, but he's going to have to re-analyze that column often if he wants this sort of query to be estimated accurately, because the fraction of entries later than a given time T is *always* going to be changing. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] make LockRelation use top transaction ID
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I just figured that if we let LockRelation use GetCurrentTransactionId() > then the wrong thing happens if we let large objects survive > subtransaction commit/abort. > So I have changed it to use GetTopTransactionId() instead. Is that OK > with everybody? No, at least not if you made that a global change. Doing it that way will mean that a failed subtransaction will not release its locks, no? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Wrong index choosen?
On Fri, 23 Jul 2004, Gaetano Mendola wrote: > empdb=# explain analyze select * from v_past_connections where login_time > > '2004-07-21'; >QUERY PLAN > -- > Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..12.90 rows=481 > width=28) (actual time=7.338..661.300 rows=22477 loops=1) > Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone) > Total runtime: 676.472 ms > (3 rows) In this plan it estimates to get 481 but it got 22477. So the estimation was very wrong. You can increase the statistics tarhet on the login_time and it will probably be better (after the next analyze). > why then the planner choose to do an index scan using the filter that > retrieve a bigger ammount of rows ? A bug ? Because it has to decide on the plan before it knows exactly what the result will be. As seen above, the estimation was wrong and thus the plan was not as good as it could have been. In this case you probably also want to create a combined index on both columns: CREATE INDEX foo ON user_log (id_user, login_time); ps. This letter belonged to pgsql-performance and not pgsql-hackers. -- /Dennis Björklund ---(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: [HACKERS] Fixing PKs and Uniques in tablespaces
> > Also, since I checked and it seems that our syntax for putting tables an > > d indexes in tablespaces at creation time is identical to oracle's, > > perhaps we should copy them on constraints as well. > > Since we're getting close to beta, can we have consensus on what I'm to > do about this? The Oracle 10g documentation has: USING INDEX TABLESPACE blabla none of the words are optional. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Wrong index choosen?
I hall I have a query in this form: empdb=# explain analyze select * from v_past_connections where id_user = 26195 and login_time > '2004-07-21'; QUERY PLAN - Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..14.10 rows=1 width=28) (actual time=66.890..198.998 rows=5 loops=1) Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone) Filter: (id_user = 26195) Total runtime: 199.083 ms (4 rows) as you see the index on the time stamp column is used The table have indexes on both columns: empdb=# explain analyze select * from v_past_connections where login_time > '2004-07-21'; QUERY PLAN -- Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..12.90 rows=481 width=28) (actual time=7.338..661.300 rows=22477 loops=1) Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone) Total runtime: 676.472 ms (3 rows) empdb=# explain analyze select * from v_past_connections where id_user = 26195; QUERY PLAN --- Index Scan using idx_user_user_logs on user_logs (cost=0.00..252.47 rows=320 width=28) (actual time=4.420..100.122 rows=221 loops=1) Index Cond: (id_user = 26195) Total runtime: 100.348 ms (3 rows) The rows filtered out with both condictions are two order of magnitude differents, also the extimated rows are close to real numbers: empdb=# select count(*) from v_past_connections where id_user = 26195; count --- 221 (1 row) empdb=# select count(*) from v_past_connections where login_time > '2004-07-21'; count --- 22441 (1 row) why then the planner choose to do an index scan using the filter that retrieve a bigger ammount of rows ? A bug ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Fixing PKs and Uniques in tablespaces
We are already in a features freeze period, or not ? This isn't a feature, it's a bug... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Fixing PKs and Uniques in tablespaces
Christopher Kings-Lynne wrote: I never really considered oracle's implementation of tablespaces when I worked on tablespaces. The database default tablespace seems similar to Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace like we do. My point was that Oracle has added a tablespace clause to the constraint clause, so we can too! Also, since I checked and it seems that our syntax for putting tables an d indexes in tablespaces at creation time is identical to oracle's, perhaps we should copy them on constraints as well. Since we're getting close to beta, can we have consensus on what I'm to do about this? We are already in a features freeze period, or not ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] make LockRelation use top transaction ID
Hackers, I just figured that if we let LockRelation use GetCurrentTransactionId() then the wrong thing happens if we let large objects survive subtransaction commit/abort. The problem is that when closing a large object at main transaction commit, which was opened inside a subtransaction, the code tries to UnlockRelation(pg_largeobject), and use the main transaction Xid (instead of the subtransaction Xid that it used to LockRelation()). So I have changed it to use GetTopTransactionId() instead. Is that OK with everybody? -- Alvaro Herrera () Maybe there's lots of data loss but the records of data loss are also lost. (Lincoln Yeoh) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
