Re: 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/

Peter Eisentraut <peter_e(at)gmx(dot)net> 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

Ühel kenal päeval, K, 2006-03-22 kell 16:11, kirjutas Tom Lane:
> Peter Eisentraut <peter_e(at)gmx(dot)net> 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

Hannu Krosing <hannu(at)skype(dot)net> writes:
> hel kenal peval, 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

Ühel kenal päeval, K, 2006-03-22 kell 17:29, kirjutas Tom Lane:
> Hannu Krosing <hannu(at)skype(dot)net> 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

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 Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

On Wednesday 22 March 2006 13:11, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> 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

Darcy Buskermolen <darcy(at)wavefire(dot)com> 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

Tom Lane said:
> Darcy Buskermolen <darcy(at)wavefire(dot)com> 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

"Andrew Dunstan" <andrew(at)dunslane(dot)net> 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

> 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

Ühel kenal päeval, K, 2006-03-22 kell 21:50, kirjutas Andrew Dunstan:
> Tom Lane said:
> > Darcy Buskermolen <darcy(at)wavefire(dot)com> 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

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 <peter_e(at)gmx(dot)net> 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 jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461