Re: [HACKERS] Accessing schema data in information schema
Ühel kenal päeval, K, 2006-03-22 kell 21:50, kirjutas Andrew Dunstan: Tom Lane said: Darcy Buskermolen [EMAIL PROTECTED] writes: On Wednesday 22 March 2006 13:11, Tom Lane wrote: (Thinks a bit...) Maybe it would work for pg_sequence to be a real catalog with a row per sequence, and we also create a view named after the sequence that simply selects from pg_sequence with an appropriate WHERE condition. I'd think that would be a workable solution, with documentation notes that this will be deprecated in favor of information_schema in an upcoming release ? Yeah, we could consider the views a transitional thing, and get rid of them after a release or two. Tell people to change over to either look in the pg_sequence catalog, or use the information_schema view. Does that view expose everything that there is, though, or will we have proprietary extensions that are not in SQL2003? What happens to sequence ACLs? perhaps we can keep pg_class part of seqs and just make the pg_class.relfilenode to point to row oid in pg_sequence table ? - Hannu ---(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: [HACKERS] Accessing schema data in information schema
On Thu, Mar 23, 2006 at 12:10:54AM +0200, Hannu Krosing wrote: ??hel kenal p??eval, K, 2006-03-22 kell 16:11, kirjutas Tom Lane: Peter Eisentraut [EMAIL PROTECTED] writes: How does one get at the missing fields. The only way I know is selecting from the sequence, but how does one work this into this query? Somehow it seems that these things should be stored in a real system catalog. Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Would it not make page locking problems much worse with all get_next()'s competeing to update the same page? What about bumping up the default cache setting a bit? Even going to a fairly conservative value, like 10 or 25 would probably make a huge difference. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Accessing schema data in information schema
Plan C would be to say that we don't need to preserve SELECT * FROM seqname, but I'll bet there would be some hollering. I'd like to hear this hollering first, before we create tons of views :-) Imho it is not a problem to remove it, I am for Plan C. (Those with need for the select can still create their view by hand. A release note would be sufficient imho.) Of course if we still need one row in pg_class for the ACL's, that row might as well be a view. Andreas ---(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: [HACKERS] Accessing schema data in information schema
Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: Of course if we still need one row in pg_class for the ACL's, that row might as well be a view. Yeah, view or view-like thingie. Given the thought that we need both transactional and nontransactional state for a sequence, I'm kind of inclined to leave the transactional data in pg_class. We could still imagine putting the nontransactional state into a new pg_sequence catalog indexed by, say, the pg_class OID of the sequences. OTOH I'm not sure how much that buys for Peter's problem --- it might be better for him just to invent some functions that can grab the required data given the sequence OID. 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: [HACKERS] Accessing schema data in information schema
Tom Lane wrote: Given the thought that we need both transactional and nontransactional state for a sequence, I'm kind of inclined to leave the transactional data in pg_class. We could still imagine putting the nontransactional state into a new pg_sequence catalog indexed by, say, the pg_class OID of the sequences. OTOH I'm not sure how much that buys for Peter's problem --- it might be better for him just to invent some functions that can grab the required data given the sequence OID. Yes, this seems a lot of lifting for a fairly small need. If there aren't other advantages, a simple function or two seems a better way to go, and then there are no legacy problems. cheers andrew ---(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
[HACKERS] Accessing schema data in information schema
I'm updating the information schema for SQL:2003. I'm having some difficulties with the sequences view. It should look approximately like this (uninteresting stuff omitted): CREATE VIEW sequences AS SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog, CAST(nc.nspname AS sql_identifier) AS sequence_schema, CAST(c.relname AS sql_identifier) AS sequence_name, CAST(null AS cardinal_number) AS maximum_value, -- FIXME CAST(null AS cardinal_number) AS minimum_value, -- FIXME CAST(null AS cardinal_number) AS increment, -- FIXME CAST(null AS character_data) AS cycle_option-- FIXME FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND c.relkind = 's'; How does one get at the missing fields. The only way I know is selecting from the sequence, but how does one work this into this query? Somehow it seems that these things should be stored in a real system catalog. Ideas (short of using PERFORM in PL/pgSQL)? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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: [HACKERS] Accessing schema data in information schema
Peter Eisentraut [EMAIL PROTECTED] writes: How does one get at the missing fields. The only way I know is selecting from the sequence, but how does one work this into this query? Somehow it seems that these things should be stored in a real system catalog. Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Unfortunately the backward-compatibility issues seem a bit daunting :-(. It's probably not completely impossible, but how do we preserve the existing behavior that you can SELECT * FROM seqname and get the parameters? Ideally I'd like SELECT * FROM seqname; -- gets params of one sequence SELECT * FROM pg_sequence; -- gets params of all sequences One possible kluge is to make all the sequences be child tables of a pg_sequence catalog that exists only to be their inheritance parent. This seems pretty ugly from a performance point of view though. Selecting from pg_sequence would be really expensive if you have a lot of sequences, and there wouldn't be any opportunity for reducing the disk footprint. (Thinks a bit...) Maybe it would work for pg_sequence to be a real catalog with a row per sequence, and we also create a view named after the sequence that simply selects from pg_sequence with an appropriate WHERE condition. Plan C would be to say that we don't need to preserve SELECT * FROM seqname, but I'll bet there would be some hollering. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Accessing schema data in information schema
Ühel kenal päeval, K, 2006-03-22 kell 16:11, kirjutas Tom Lane: Peter Eisentraut [EMAIL PROTECTED] writes: How does one get at the missing fields. The only way I know is selecting from the sequence, but how does one work this into this query? Somehow it seems that these things should be stored in a real system catalog. Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Would it not make page locking problems much worse with all get_next()'s competeing to update the same page? At least unless you reserve one page for each sequence. - Hannu ---(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: [HACKERS] Accessing schema data in information schema
Hannu Krosing [EMAIL PROTECTED] writes: Ãhel kenal päeval, K, 2006-03-22 kell 16:11, kirjutas Tom Lane: Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Would it not make page locking problems much worse with all get_next()'s competeing to update the same page? Well, there'd be at most about 80 sequences per page (ballpark estimate remembering that we'd still want to store a sequence name) and the reduction in demand for shared buffers might outweigh the increased contention for any one buffer. I haven't seen any examples where get_next is the key source of contention anyhow. A last point is that in simple cases where the contention is all on one sequence, you're going to have that problem anyway. At least unless you reserve one page for each sequence. Which is exactly what I don't want. But we could imagine padding the tuples to achieve any particular tuples/page ratio we want, if 80 proves to be uncomfortably many. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Accessing schema data in information schema
Ühel kenal päeval, K, 2006-03-22 kell 17:29, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: Ühel kenal päeval, K, 2006-03-22 kell 16:11, kirjutas Tom Lane: Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Would it not make page locking problems much worse with all get_next()'s competeing to update the same page? Well, there'd be at most about 80 sequences per page (ballpark estimate remembering that we'd still want to store a sequence name) and the reduction in demand for shared buffers might outweigh the increased contention for any one buffer. I haven't seen any examples where get_next is the key source of contention anyhow. Probably true. I can't think of one right now either. And we have caching to solve these cases. A last point is that in simple cases where the contention is all on one sequence, you're going to have that problem anyway. At least unless you reserve one page for each sequence. Which is exactly what I don't want. But we could imagine padding the tuples to achieve any particular tuples/page ratio we want, if 80 proves to be uncomfortably many. I guess we can't easily start locking some subarea of a page, say 256 byte subpage, or just the tuple. OTOH it may be possible as we don't need to lock page header for sequences as the tuple is updated in place and will not change in size. OTOOH, I'm afraid we still need to WAL the whole page, so the savings will be marginal. Hannu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Accessing schema data in information schema
Hannu Krosing wrote: I guess we can't easily start locking some subarea of a page, say 256 byte subpage, or just the tuple. OTOH it may be possible as we don't need to lock page header for sequences as the tuple is updated in place and will not change in size. Huh, we _can_ lock individual tuples, using LockTuple() (or rather, heap_lock_tuple). Since the tuple is modified in place, there's no need to lock the whole page. OTOOH, I'm afraid we still need to WAL the whole page, so the savings will be marginal. Huh, why? We can just keep the current WAL logging for sequences, or something very similar, can't we? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Accessing schema data in information schema
Alvaro Herrera [EMAIL PROTECTED] writes: Hannu Krosing wrote: I guess we can't easily start locking some subarea of a page, say 256 byte subpage, or just the tuple. Huh, we _can_ lock individual tuples, using LockTuple() (or rather, heap_lock_tuple). Since the tuple is modified in place, there's no need to lock the whole page. But heap_lock_tuple is pretty expensive and subject to deadlocks. I think getting the buffer content lock on the page will still be the right thing. OTOOH, I'm afraid we still need to WAL the whole page, so the savings will be marginal. Huh, why? We can just keep the current WAL logging for sequences, or something very similar, can't we? In the case of the first touch of a sequence page after checkpoint, we'd need to WAL the whole page image to defend against page breaks during write. After that though the WAL entries would be *smaller* than they are now, since there'd be no need to log the entire content of the changed tuple; we'd know we only need to log the counter advance. It's hard to say whether this'd be a win, loss, or wash without testing. It'd probably depend on how many nextval's per checkpoint you want to assume. 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: [HACKERS] Accessing schema data in information schema
On Wednesday 22 March 2006 13:11, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: How does one get at the missing fields. The only way I know is selecting from the sequence, but how does one work this into this query? Somehow it seems that these things should be stored in a real system catalog. Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Unfortunately the backward-compatibility issues seem a bit daunting :-(. It's probably not completely impossible, but how do we preserve the existing behavior that you can SELECT * FROM seqname and get the parameters? Ideally I'd like SELECT * FROM seqname; -- gets params of one sequence SELECT * FROM pg_sequence; -- gets params of all sequences One possible kluge is to make all the sequences be child tables of a pg_sequence catalog that exists only to be their inheritance parent. This seems pretty ugly from a performance point of view though. Selecting from pg_sequence would be really expensive if you have a lot of sequences, and there wouldn't be any opportunity for reducing the disk footprint. (Thinks a bit...) Maybe it would work for pg_sequence to be a real catalog with a row per sequence, and we also create a view named after the sequence that simply selects from pg_sequence with an appropriate WHERE condition. I'd think that would be a workable solution, with documentation notes that this will be deprecated in favor of information_schema in an upcoming release ? Plan C would be to say that we don't need to preserve SELECT * FROM seqname, but I'll bet there would be some hollering. ? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Accessing schema data in information schema
Darcy Buskermolen [EMAIL PROTECTED] writes: On Wednesday 22 March 2006 13:11, Tom Lane wrote: (Thinks a bit...) Maybe it would work for pg_sequence to be a real catalog with a row per sequence, and we also create a view named after the sequence that simply selects from pg_sequence with an appropriate WHERE condition. I'd think that would be a workable solution, with documentation notes that this will be deprecated in favor of information_schema in an upcoming release ? Yeah, we could consider the views a transitional thing, and get rid of them after a release or two. Tell people to change over to either look in the pg_sequence catalog, or use the information_schema view. Does that view expose everything that there is, though, or will we have proprietary extensions that are not in SQL2003? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Accessing schema data in information schema
Tom Lane said: Darcy Buskermolen [EMAIL PROTECTED] writes: On Wednesday 22 March 2006 13:11, Tom Lane wrote: (Thinks a bit...) Maybe it would work for pg_sequence to be a real catalog with a row per sequence, and we also create a view named after the sequence that simply selects from pg_sequence with an appropriate WHERE condition. I'd think that would be a workable solution, with documentation notes that this will be deprecated in favor of information_schema in an upcoming release ? Yeah, we could consider the views a transitional thing, and get rid of them after a release or two. Tell people to change over to either look in the pg_sequence catalog, or use the information_schema view. Does that view expose everything that there is, though, or will we have proprietary extensions that are not in SQL2003? What happens to sequence ACLs? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Accessing schema data in information schema
Andrew Dunstan [EMAIL PROTECTED] writes: What happens to sequence ACLs? Hm, good point. We could put 'em in pg_sequence, except that most of the operations on pg_sequence rows will be nontransactional, and that doesn't seem to square nicely with transactional updates on ACLs. Maybe we need two catalogs just to separate the transactional and nontransactional data for a sequence? Ugh. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Accessing schema data in information schema
Hm, good point. We could put 'em in pg_sequence, except that most of
the operations on pg_sequence rows will be nontransactional, and that
doesn't seem to square nicely with transactional updates on ACLs.
Maybe we need two catalogs just to separate the transactional and
nontransactional data for a sequence? Ugh.
Is it possible to have an SRF that can peek into the lastval data and
present it, and make no changes to our catalogs at all?
Or can't we use in the schema view something like:
CREATE VIEW sequences AS
SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
CAST(nc.nspname AS sql_identifier) AS sequence_schema,
CAST(c.relname AS sql_identifier) AS sequence_name,
(SELECT seq_info('sequence_name', 'max')) AS maximum_value,
(SELECT seq_info('sequence_name', 'min')) AS minimum_value,
(SELECT seq_info('sequence_name', 'inc')) AS increment,
(SELECT seq_info('sequence_name', 'cycle')) AS cycle_option
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind = 's';
Chris
---(end of broadcast)---
TIP 6: explain analyze is your friend
